package gen.tools;

import static java.lang.System.out;
import gen.tools.deploy.QDPS;
import gen.tools.deployByDelivery.FileStr;
import gen.tools.structures.DepFileNode;
import gen.tools.structures.I1S1;
import gen.tools.structures.L1S1;
import gen.tools.structures.ModNode;
import gen.tools.structures.OpenCRA;
import gen.tools.structures.Req;
import gen.tools.structures.S1AS2;
import gen.tools.structures.S2;
import gen.tools.structures.S3;
import gen.tools.structures.S4;
import gen.tools.structures.S5;
import gen.tools.structures.S8;
import gen.tools.structures.SquadParis;
import gen.tools.structures.Subsys4Maintain;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.mail.Message.RecipientType;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;

import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.UsernamePasswordCredentials;
import org.apache.commons.httpclient.auth.AuthScope;
import org.apache.commons.httpclient.methods.PostMethod;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.SAXReader;
import org.dom4j.io.XMLWriter;
import org.jboss.as.cli.CommandContext;
import org.jboss.as.cli.CommandContextFactory;
import org.tmatesoft.svn.core.ISVNDirEntryHandler;
import org.tmatesoft.svn.core.SVNDirEntry;
import org.tmatesoft.svn.core.SVNException;
import org.tmatesoft.svn.core.SVNLogEntry;
import org.tmatesoft.svn.core.SVNLogEntryPath;
import org.tmatesoft.svn.core.SVNNodeKind;
import org.tmatesoft.svn.core.SVNProperties;
import org.tmatesoft.svn.core.SVNURL;
import org.tmatesoft.svn.core.auth.ISVNAuthenticationManager;
import org.tmatesoft.svn.core.internal.io.dav.DAVRepositoryFactory;
import org.tmatesoft.svn.core.internal.wc.DefaultSVNOptions;
import org.tmatesoft.svn.core.io.SVNRepository;
import org.tmatesoft.svn.core.io.SVNRepositoryFactory;
import org.tmatesoft.svn.core.wc.SVNClientManager;
import org.tmatesoft.svn.core.wc.SVNCommitClient;
import org.tmatesoft.svn.core.wc.SVNLogClient;
import org.tmatesoft.svn.core.wc.SVNRevision;
import org.tmatesoft.svn.core.wc.SVNWCUtil;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.google.gson.reflect.TypeToken;
import com.jcraft.jsch.Channel;
import com.jcraft.jsch.ChannelExec;
import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.SftpException;

/**
 * @author Arthur
 *
 */
public class Widgets {
	
	class DepEntryHandler implements ISVNDirEntryHandler {
		public void handleDirEntry(SVNDirEntry entry) throws SVNException {
			String parentDir = null;
			final String path = entry.getRelativePath();
			
			if (!path.equalsIgnoreCase("")) {
				
				if (path.contains("/")) {
					parentDir= path.substring(0,path.lastIndexOf("/"));
				} else {
					parentDir = "rroott";
				}
				
				if ( entry.getKind() == SVNNodeKind.DIR ) {
					//名和值都有單引號
					res += "{'id':'"+path+"','pId':'"+parentDir+"','open':'true','name':'"+entry.getName()+"','icon':'./pic/folder.gif'},";
				} else if ( entry.getKind() == SVNNodeKind.FILE && (entry.getName().endsWith(".war") || entry.getName().endsWith(".ear") ) ) {
					res += "{'id':'"+path+"','pId':'"+parentDir+"','name':'"+entry.getName()+"','icon':'./pic/file.png','verSets':''},";
				}
			}
		}
	}
	/**
	 *		存取SVN所需要的Class
	 *		<br>創建時間：2013/9/24 下午6:44:15<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	class DirEntryHandler implements ISVNDirEntryHandler {
		public void handleDirEntry(SVNDirEntry dirEntry) throws SVNException {
			if ( dirEntry.getKind() == SVNNodeKind.DIR ) { //僅有目錄方能被選擇
				String parentDir = null;
				final String path = dirEntry.getRelativePath();
				
				if (!path.equalsIgnoreCase("")) {
					if (!path.contains("/")) {
						parentDir = "root";
					} else {
						parentDir = path.substring(0,path.lastIndexOf(dirEntry.getName()) -1);
					}
					//名和值都有單引號
					res += "{'id':'"+path+"','pId':'"+parentDir+"','name':'"+dirEntry.getName()+"','icon':'./pic/folder.gif'},";
				}
			}
		}
	}
	/**
	 *		存取SVN產生檔案結構時所需要的Class
	 *		<br>創建時間：2013/10/31 上午9:35:56<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	class FileEntryHandler implements ISVNDirEntryHandler {
		public void handleDirEntry(SVNDirEntry entry) throws SVNException {
			String parentDir = null;
			final String path = entry.getRelativePath();
			
			if (!path.equalsIgnoreCase("")) {
				if (path.contains("/")) {
					parentDir= path.substring(0,path.lastIndexOf("/"));
				} else {
					parentDir = "rroott";
				}
				
				if ( entry.getKind() == SVNNodeKind.DIR ) {
					//名和值都有單引號
					res += "{'id':'"+path+"','pId':'"+parentDir+"','open':'true','name':'"+entry.getName()+"','icon':'./pic/folder.gif'},";
				} else if ( entry.getKind() == SVNNodeKind.FILE ) {
					res += "{'id':'"+path+"','pId':'"+parentDir+"','name':'"+entry.getName()+"','icon':'./pic/file.png'},";
				}
			}
		}
	}
	//final public String JENKINSURL = "http://10.144.97.100:8080/jenkins/";
	final public String APSERVER_HOST = "http://10.144.97.100:8080/DevOps/";
	final private String dateFormat = "yyyy-MM-dd HH:mm:ss";
	final public String DBcodeSet = "Big5";				//後端資料庫的編碼
	
	final public String ftpIP = "10.144.97.99";
	final public String ftpPWD = "arthur7382";
	final public String ftpUsr = "Arthur";
	final public boolean IS_STRICT=true; //只能回傳大於等於的版本
	
	boolean isCodingEqual = false;					//前端與後端的編碼是否一致
	final public String JENKINS_PWD = "arthymoment";
	final public String JENKINS_USR = "arthymoment";
	final public String JENKINSURL = "http://10.144.97.99:8330/jenkins/";
	final public String JNDINAME = "java:jboss/datasources/DPS";
	
	final private int LATEST = -1;					//SVN中最新的版本，皆輸入-1
    final private String mailLocale="big5";	//信件的編碼
    final private String mailServer="mail.smtp.host";
	
	final private String mailServerIP="10.160.1.1";
	String res = "[";
	public final String splitTokenFOUR = "PPPP";
	
	public final String splitTokenONE = "UUUU";
	
	public final String splitTokenTHREE = "XXXX";
	public final String splitTokenTWO = "TTTT";
	
	final public String SYSID = "sys01";
	
	//final public String DBcodeSet = "UTF8";			//後端資料庫的編碼
	//final public String DBcodeSet = "ISO-8859-1";		//後端資料庫的編碼
	final public String TOKENONE = "PPPP";
	
	final public String UIcodeSet = "UTF-8";			//前端HTML的編碼
	
	final public int WAITING = 3000;	//等待間隔秒數
	
	
	public Widgets() {
		isCodingEqual = UIcodeSet.equalsIgnoreCase(DBcodeSet);
	}
	
	/**
	 * 從加入的點開始，查出以降的所有Dependacy
	 *	@param nex
	 *	@return
	 *	@throws IOException	
	 *	<br>創建時間：2013/11/21 下午5:28:07<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public JobDepStruc buildTree (JobDepStruc nex) throws IOException {
		final String urlStr = JENKINSURL+"job/"+(nex.isMR()?"MR":"OL")+"_"+nex.getSubsysId()+"_"+nex.getModId()+"/config.xml";
		
		final JobDepStruc selDbls = new JobDepStruc(nex.getModId(),nex.isMR(),nex.getSubsysId());
		selDbls.setNextBuildNumber(selDbls.nextBuildNumber());
		selDbls.setUp(nex);
		final Map<String,Object> map = new HashMap<String,Object>();
        
        // 連線取得資料
        InputStream res = null; 
        final SAXReader reader = new SAXReader();
        try {
        	res = URLConnectionUtil.doGet(urlStr,false,JENKINS_USR,JENKINS_PWD,map);
        	final Document document = reader.read(res);
    		final Element root = document.getRootElement();
    		final Iterator<Element> it = root.elementIterator();	
    		final ArrayList<JobDepStruc> dbss = new ArrayList<JobDepStruc>();
        	while (it.hasNext()) {
    			final Element e2 = it.next();
    			if ( "publishers".equalsIgnoreCase(e2.getName() ) ) {
    				final Iterator<Element> it2 = e2.elementIterator();
    				while (it2.hasNext()) {
    					final Element e3 = it2.next();
    					if ( "hudson.tasks.BuildTrigger".equalsIgnoreCase(e3.getName() ) ) {
    						final String tmpStr = e3.element("childProjects").getText();
        					final String[] downStreamModId = tmpStr.split(",");	//因為可能下游不只一個
        					
        					for(int i=0;i<downStreamModId.length;i++){
        						final String ds = downStreamModId[i].trim();
        						final String downStreamId = ds.substring(ds.lastIndexOf("_")+1, ds.length());
        						final JobDepStruc nxs = new JobDepStruc(downStreamId,nex.isMR(),nex.getSubsysId());
        						nxs.setUp(selDbls);
        						dbss.add( buildTree(nxs) );
        					}
    					}
    				}
    			}
    		}
        	selDbls.setDown(dbss);
        	selDbls.setLeaf( ( dbss.size()== 0) );
        	
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
        	res.close();
        }
		
		return selDbls;
	}
	
	/**
	 *	@return	當下的時間戳記
	 *	<br>創建時間：2013/10/3 下午5:28:50<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public Timestamp cDT() {
	    return Timestamp.valueOf(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
	}
			
	/**
	 *		將日期轉換為"預設的格式"輸出
	 *		<br>創建時間：2013/9/27 上午10:06:07<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public String changeTimeFormat(Date dt) {
		return new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(dt);
	}
	
	/**
	 *		將日期轉換為"指定的格式"輸出
	 *		<br>創建時間：2013/9/27 上午10:05:35<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public String changeTimeFormat(Date dt, String format) {
		return new java.text.SimpleDateFormat(format).format(dt);
	}
	
	/**
	 *		直接將日期轉為前端UI偏好的排版格式輸出(這裡所為的偏好是by專案不同的)
	 *		<br>創建時間：2013/9/27 上午10:06:34<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public String changeTimeFormatUI(Date dt) {
		return new java.text.SimpleDateFormat("yyyy-MM-dd").format(dt)+"<br>"+new java.text.SimpleDateFormat("HH:mm:ss").format(dt);
	}
	/**	取得指定檔案的版次歷程
	 *	@param url
	 *	@param usr
	 *	@param pwd
	 *	@return
	 *	@throws SVNException	
	 *	<br>創建時間：2013/11/18 上午11:41:44<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public ArrayList<Long> chkFileRevision(String url,String usr,String pwd) throws SVNException {
		final ArrayList<Long> verSet = new ArrayList<Long>();

		final SVNRepository repo = SVNRepositoryFactory.create(SVNURL.parseURIEncoded(url));
		final ISVNAuthenticationManager authManager = SVNWCUtil.createDefaultAuthenticationManager(usr,pwd);
		repo.setAuthenticationManager(authManager);
		Set<?> changedPathsSet = null;
		final Collection<?> logEntries = repo.log( new String[] { "" } ,null , (long)1 , (long)-1 , true , true );
        for ( Iterator<?> entries = logEntries.iterator( ); entries.hasNext( ); ) {
        	final SVNLogEntry logEntry = ( SVNLogEntry ) entries.next( );
            if ( logEntry.getChangedPaths( ).size( ) > 0 ) {
            	changedPathsSet = logEntry.getChangedPaths( ).keySet( );
                for ( Iterator<?> changedPaths = changedPathsSet.iterator( ); changedPaths.hasNext( ); ) {
                	final SVNLogEntryPath entryPath = ( SVNLogEntryPath ) logEntry.getChangedPaths( ).get( changedPaths.next( ) );
                    if ( (entryPath.getType()=='A' || entryPath.getType()=='M') && entryPath.getPath( ).indexOf("/"+"")!=-1) {
                    	if ( !verSet.contains(logEntry.getRevision()) ) {
                    		verSet.add( logEntry.getRevision() );
                    	}
                    }
                }
            }
        }
        Collections.reverse(verSet);
        repo.closeSession();
        
		return verSet;
	}
	
	/** 取得指定檔案的版次歷程與該版次的註解
	 *	@param url
	 *	@param usr
	 *	@param pwd
	 *	@param isStrict	是否限制只回傳大於等於先前部署的版次
	 *	@return
	 * @throws Exception 
	 */
	public ArrayList<L1S1> chkFileRevisionAndComment(boolean isMR,String url,String usr,String pwd,String relativePath,String fileName,boolean isStrict) throws Exception {
		final ArrayList<L1S1> verSets = new ArrayList<L1S1>();
		int baseVer = 0;
		if( isStrict ) {
			baseVer = getMAXfileVer(isMR,relativePath,fileName );
		}
		final SVNRepository repo = SVNRepositoryFactory.create(SVNURL.parseURIEncoded(url+relativePath));
		final ISVNAuthenticationManager authManager = SVNWCUtil.createDefaultAuthenticationManager(usr,pwd);
		repo.setAuthenticationManager(authManager);
		final long startRevision = 0;
		final long endRevision = -1;
		
		final Collection<?> logEntries = repo.log( new String[] { fileName } , null, startRevision , endRevision , true , true );
		
		for ( Iterator entries = logEntries.iterator( ); entries.hasNext( ); ) {
			final SVNLogEntry logEntry = ( SVNLogEntry ) entries.next( );
			if ( logEntry.getRevision() >= baseVer ) {
				final L1S1 vs = new L1S1();
        		vs.setL1( logEntry.getRevision() );
        		vs.setS1( logEntry.getMessage() );
        		verSets.add( vs );	
    		}
		}
       
        Collections.reverse(verSets);
        repo.closeSession();
		return verSets;
	}
	
	/*
	 * 0 尚未測試	
	 * 2 主機不能連
	 * 3 帳密不通
	 * 4 連線正常
	 * 5不存在指定路徑
	 * */
	public int chkFTPconnection(String url,String usr,String pwd,String path) throws JSchException {
		int res =0;
		
		final JSch jsch = new JSch();
		out.println(111);
        JSch.setConfig("StrictHostKeyChecking", "no");
        com.jcraft.jsch.Session session = null;
        ChannelSftp sftp = null;
		
        
        try {
        	session = jsch.getSession(usr, url);
    		session.setPassword( pwd );
    		try {
    			out.println("ready to connect to FTP");
    			session.connect();
    			out.println(666);
    			sftp = (ChannelSftp) session.openChannel("sftp");
    			sftp.connect();
    			sftp.cd("/");
    			System.out.println("目前的位址："+sftp.pwd());
    			cwdButNotmkdir(sftp,path);
    			res = 4;
    		} catch (Exception e) {
    			if ( "Auth fail".equalsIgnoreCase(e.getMessage()) ) {
    				res=3;	//帳密不通	
    			} else if(e.getMessage().contains("Connection timed out: connect") ) {
    				res=2;	//主機不能連
    			} else if ( "No such file".equalsIgnoreCase(e.getMessage()) ) {
    				res=5;	//不存在指定路徑
    			} else {
    				errLog(e);
    			}
    		}
        } catch (Exception e) {
        	errLog(e);
        	res=2;
        } finally {
        	if ( sftp != null ) { if ( sftp.isConnected() ) { sftp.disconnect(); } }
			if ( session != null ) { if ( session.isConnected() ) { session.disconnect(); } }
        }
        
        return res;
	}
	
	/*
	 * 0 尚未測試	
	 * 1 帳密不通
	 * 2 主機不能連
	 * 99 連線正常
	 * */
	public int chkHostConnection(String url,String usr,String pwd) throws JSchException {
		int res =0;
		
		final JSch jsch = new JSch();
        JSch.setConfig("StrictHostKeyChecking", "no");
        com.jcraft.jsch.Session session = null;
		
        try {
        	session = jsch.getSession(usr, url, 22);
        	session.setPassword(pwd);
    		try {
    			session.connect();
    			res = 99;
    		} catch (Exception e) {
    			if ( "Auth cancel".equalsIgnoreCase(e.getMessage()) || e.getMessage().contains("Auth fail") ) {
    				res=1;	//帳密不通	
    			} else if(e.getMessage().contains("java.net.UnknownHostException") || e.getMessage().contains("Connection timed out: connect") ) {
    				res=2;	//主機不能連
    			}
    			System.out.println(e.toString());
    		}
        } finally {
			if ( session != null ) { if ( session.isConnected() ) { session.disconnect(); } }
        }
        
        return res;
	}
	
	/**
	 * 	取得指定JOB的Build版封裝結果
	 *	@param jobName
	 *	@param jobSN
	 *	@param subsysId
	 *	@return
	 *	@throws Exception	
	 *	<br>創建時間：2013/11/22 下午3:57:27<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public String chkJenkinsJobResult(String jobName,int jobSN) throws Exception {
		String res = "";
		
		final String urlStr = JENKINSURL+"job/"+jobName+"/"+jobSN+"/api/xml";
		final Map<String,Object> map = new HashMap<String,Object>();
		
		final InputStream ins = URLConnectionUtil.doGet(urlStr,false,JENKINS_USR,JENKINS_PWD,map);
		final SAXReader reader = new SAXReader();
		final Document document = reader.read(ins);
		ins.close();
		
		Element root = document.getRootElement();
		Iterator<Element> it = root.elementIterator();
		boolean isDone = false;
    	while (it.hasNext()) {
			Element e = it.next();
			res = e.getText();
			isDone = true;
		}
    	
    	if( !isDone ) {
    		Thread.currentThread().wait( WAITING );
    		res = chkJenkinsJobResult(jobName,jobSN);
    	}
    	
		return res;
	}
	
	public String chkServiceStatus(String Url,String Id,String Pwd) throws Exception {
		String res = "";
		InputStream in = null;
		Channel channel = null;
		com.jcraft.jsch.Session session = null;
		try {
			final JSch jsch = new JSch();
			final Properties config = new java.util.Properties();
			
			config.put("StrictHostKeyChecking", "no");
			
			session = jsch.getSession(Id, Url, 22);
			session.setConfig(config);
			session.setPassword(Pwd);
			session.connect();
			
			String cmd = "";
			
			if ("root".equalsIgnoreCase(Id)) {
				cmd = "service httpd status";
			} else {
				cmd = "sudo service httpd status";
			}
			
			channel = session.openChannel("exec");
			((ChannelExec) channel).setCommand( cmd );

			channel.setInputStream(null);

			((ChannelExec) channel).setErrStream(System.err);

			in = channel.getInputStream();

			channel.connect();

			final byte[] tmp = new byte[1024];
			while (true) {
				while (in.available() > 0) {
					int i = in.read(tmp, 0, 1024);
					if (i < 0)
						break;
					res += new String(tmp, 0, i);
				}
				if (channel.isClosed()) {
					break;
				}
				try {
					Thread.sleep(1000);
				} catch (Exception ee) {
				}
			}
			channel.disconnect();
			session.disconnect();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if ( in != null) { in.close(); }
			if ( channel != null ) { if ( channel.isConnected() ) { channel.disconnect(); } }
			if ( session != null ) { if ( session.isConnected() ) { session.disconnect();} }
		}
		
		return res;
	}
	
	/**
	 * 		測試欲連線的SVN，測試是否能正常連線
	 * 		@param url 連線的SVN主機位址
	 * 		@param usr 連線帳號
	 * 		@param pwd 連線密碼
	 *		@return 0 尚未測試  2 主機不能連 3 帳密不通  4 連線正常  (1已挪做他用)
	 *		<br>創建時間：2013/9/26 上午10:45:00<br>
	 *		創建人員：郭啟揚 (842297)
	 * 		@throws SVNException 
	 */
	public int chkSVNconnection(String url,String usr,String pwd) throws SVNException {
		int resSVN4subsys = 0;
		
		final SVNRepository repo = SVNRepositoryFactory.create(SVNURL.parseURIEncoded(url));
		final ISVNAuthenticationManager authManager = SVNWCUtil.createDefaultAuthenticationManager(usr,pwd);
		repo.setAuthenticationManager(authManager);
		
		try {
			repo.getRepositoryPath("");	//執行查詢相關路徑位址，以驗證連結成功
			
			//能執行到以下這行表示連線成功
			resSVN4subsys=4;
		} catch (SVNException e) {
			if ( e.toString().contains("E170001")) {
				resSVN4subsys=3;	//帳密不通
			} else if ( e.toString().contains("E175002")) {
				resSVN4subsys=2;	//主機連線失敗
			}	
		}
		repo.closeSession();
		return resSVN4subsys;
	}
	
	/**
	 * 刪除已完成的待辦事項，將其加入待辦清單
	 * @param toDoListId	待辦事項的編號
	 * @param applierId		申請該事項的人員ID<p>
	 * Commented on:2014/3/12 上午9:38:55<br>
	 * Created by:  Arthur
	 */
	public void completedList(int toDoListId, String applierId) {
		//加入完成清單，因為這個SQL中，是用 SELECT todolist組成，所以必須在下個刪除動作之前，否則會沒東西INSERT
		String sql = "INSERT INTO completedlist (completedId,subsysId,roleId,dataType,masterId,completorId,cDT,eDT) SELECT todolistId,subsysId,roleId,dataType,masterId,'"+applierId+"',cDT,'"+cDT().toString()+"' FROM todolist WHERE todolistId=?";
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, toDoListId);
			System.out.println("toDoListId:"+toDoListId+"\tapplierId："+applierId);
			System.out.println("SQL:"+sql);
			System.out.println("執行了新增completedList："+ps.execute() );
			
			
			
			//刪除toDoList項目
			sql = "DELETE FROM todolist WHERE toDoListId=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, toDoListId);
			System.out.println("SQL:"+sql);
			System.out.println("執行了刪除todolist："+ps.execute() );
			ps.execute();	
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		
	}
	
	
	/**	記錄Build版封裝執行的記錄
	 * @param jobName
	 * @param lastBuildNumber
	 * @param ujn
	 * @param ujsn
	 * @param chkInId
	 * @param performerId
	 * @param isDailyBuild
	 * @throws Exception
	 */
	public void createBElog(String jobName, int lastBuildNumber,String ujn,int ujsn,String rootJobName,int rootJobBuildNumber,int chkInId,String performerId,boolean isDailyBuild) throws Exception {
		final Connection conn = getConn();
		PreparedStatement ps = null;
		
		final String sql = "INSERT INTO bemain (modId,jobSN,modIdp,jobSNp,chkInMainId,beStatusId,isManual,creatorId,bDT,eDT,isDailyBuild,rootJobName,rootJobSN) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		final int duration = Integer.parseInt( getJenkinsJobResult(jobName, lastBuildNumber,"estimatedDuration") );
		final long timeStamp = Long.parseLong( getJenkinsJobResult(jobName, lastBuildNumber,"timestamp") );
		
		if( jobName.equalsIgnoreCase(rootJobName) ) {	//如果這個job就是被觸發的job(即rootJob), 則將上游job都設為空與0 
			ujn = "";
			ujsn = 0;
		}
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, jobName);
			ps.setInt(2, lastBuildNumber);
			ps.setString(3, ujn);
			ps.setInt(4, ujsn);
			ps.setInt(5, chkInId);
			ps.setInt(6, 1 );	//封裝的結果
			ps.setString(7,chkInId==0?"t":"f");							//是否為手動部署
			ps.setString(8, performerId);
			ps.setTimestamp(9, new Timestamp(timeStamp) );
			ps.setTimestamp(10, new Timestamp(timeStamp+duration) );
			ps.setString(11, isDailyBuild?"t":"f");
			ps.setString(12, rootJobName);
			ps.setInt(13, rootJobBuildNumber);
			ps.execute();
		} catch (SQLException e) {
			errLog(e);	
		} catch (Exception e) {
			throw e;
		} finally {
			ps.close();
			conn.close();
		}
		
		
	}
	
	
	/**
	 *		在SVN上創建資料夾
	 *		@param dir 欲創建的資料夾名稱
	 *		@param isCascade	是否一併創建相關的資料夾
	 *		@return 0 尚未動作 2 主機不能連 3 帳密不通 4 dir變數有錯  5 新增成功 6 url變數有錯 7 該夾已存在
	 *		<br>ex: 登入的repo，是  10.144.97.100/arthur，但提供的參數url，是 10.144.97.100/arthur/aaa/bbb<br>此時假設參數dir為ccc，若isCascade為true，
	 *		則程式將一併創建aaa,bbb，若為false，則回傳9 (錯誤)<br>
	 *		即 程式將創建  url+dir 內的所有資料夾，但若isCascade為false，則僅創建repo下的 dir夾(但dir仍可多層 ex: mmm/kkk/rrr，表示依序創建三個夾)
	 *		<br>創建時間：2013/10/2 上午9:30:13<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public int creDirInSVN(String url,String usr,String pwd,String dir,boolean isCascade ) throws SVNException {
		
		int res=0;
		String[] arrDir = null;

		
		/** 變數整理 **************/ 
		if (dir.indexOf("/") == 0) {		//使用者可能用 /開頭  
			dir = dir.substring(1, dir.length() );
		}
		if (dir.lastIndexOf("/") == dir.length()-1) {  //使用者可能用 /結尾
			dir = dir.substring(0, dir.length()-1);
		}
		
		//若還檢查出頭尾是 /，表示真的是格式錯誤
		if (dir.indexOf("/") == 0 || (dir.lastIndexOf("/") == dir.length()-1) ) {  
			return 4;
		}
		
		//中間含 / 可以接受
		if ( dir.contains("/") ) {	
			arrDir = dir.split("/");
			for(int i=0; i<arrDir.length;i++ ) {
				if ( !isValidDirName(arrDir[i])) {	//單個夾必須符合命名規範
					return 4;
				}
			}
		} else { 
			if ( !isValidDirName(dir)) {	//必須符合命名規範
				return 4;
			}
		}
		//使用者在最後加的/，將擾亂repo的判斷，拿掉!
		if (url.lastIndexOf("/") == url.length()-1) {	
			url = url.substring(0, url.length()-1);
		}
		/************** 變數整理 **/ 
		
		final DefaultSVNOptions options = new DefaultSVNOptions();
		final SVNClientManager manager = SVNClientManager.newInstance(options,usr,pwd); //如果需要用戶名密碼
		final SVNCommitClient svnCommitClient = manager.getCommitClient();
		final SVNRepository repo = SVNRepositoryFactory.create(SVNURL.parseURIEncoded(url));
		final ISVNAuthenticationManager authManager = SVNWCUtil.createDefaultAuthenticationManager(usr,pwd);
		repo.setAuthenticationManager(authManager);
		
		try {
			repo.getRepositoryPath("");	//執行查詢相關路徑位址，以驗證連結成功
		} catch (SVNException e) {
			if ( e.toString().contains("E170001")) {
				res=3;	//帳密不通
			} else if ( e.toString().contains("E175002")) {
				res=2;	//主機連線失敗
			}
			return res;
		}
		
		String repoRoot = repo.getRepositoryRoot(true).toString();
		final int repoRootLength = repoRoot.length();
		
		String relativePath = ""; //為檢查該夾是否存在
		
		if ( repoRootLength == url.length() ) { //表示所給的連線url，即是repo
			if ( arrDir==null ) {
				if ( repo.checkPath("/"+dir, -1) == SVNNodeKind.NONE ) {
					svnCommitClient.doMkDir(new SVNURL[]{SVNURL.parseURIEncoded(url+"/"+dir)}, null);
					res =5;
				} else {
					res =7;
				}
			} else {
				for(int i=0; i<arrDir.length;i++ ) {
					relativePath += "/" + arrDir[i];
					url += "/" + arrDir[i];
					if ( repo.checkPath(relativePath, -1) == SVNNodeKind.NONE ) {
						svnCommitClient.doMkDir(new SVNURL[]{SVNURL.parseURIEncoded(url)}, null);
						res =5;
					} else {
						res =7;
					}
				}
			}
		} else {
			//實際上連入SVN後，目前的位置可能在 repo中，根本不存在
			String location = url.substring(repoRootLength+1, url.length());	//repo字串外的字串，加1 是要跳過/
			if ( location.contains("/") ) {	//表示不少層
				if( isCascade ) {
					final String[] arrUrlExt = location.split("/");
					for(int i=0; i<arrUrlExt.length;i++ ) {
						if ( !isValidDirName(arrUrlExt[i])) {	//單個夾必須符合命名規範
							return 6;
						}
					}
					for(int i=0; i<arrUrlExt.length;i++ ) {	//處理url中多處來的
						relativePath += "/" + arrUrlExt[i];
						repoRoot += "/" + arrUrlExt[i];
						if ( repo.checkPath(relativePath, -1) == SVNNodeKind.NONE ) {
							svnCommitClient.doMkDir(new SVNURL[]{SVNURL.parseURIEncoded(repoRoot)}, null);	
						}
					}
					
					//接著處理 真正欲創建的資料夾字串
					if ( arrDir==null ) {
						if ( repo.checkPath("/"+location+"/"+dir, -1) == SVNNodeKind.NONE ) {
							svnCommitClient.doMkDir(new SVNURL[]{SVNURL.parseURIEncoded(repoRoot+"/"+dir)}, null);
							res =5;
						} else {
							res =7;
						}
					} else {
						for(int i=0; i<arrDir.length;i++ ) {
							relativePath += "/" + arrDir[i];
							repoRoot += "/" + arrDir[i];
							if ( repo.checkPath(relativePath, -1) == SVNNodeKind.NONE ) {
								svnCommitClient.doMkDir(new SVNURL[]{SVNURL.parseURIEncoded(repoRoot)}, null);
								res =5;
							} else {
								res=7;	//整個for loop跑完， 最後若res為7，表示這整串都新增過了
							}
						}
					}
				} else {
					return 6;
				}
			} else {
				if ( !isValidDirName(location) ) {
					return 6;
				} else {
					if ( repo.checkPath("/"+location, -1) == SVNNodeKind.NONE ) {
						svnCommitClient.doMkDir(new SVNURL[]{SVNURL.parseURIEncoded(url)}, null);
					}
					//接著處理 真正欲創建的資料夾字串
					if ( arrDir==null ) {
						if ( repo.checkPath("/"+location+"/"+dir, -1) == SVNNodeKind.NONE ) {
							svnCommitClient.doMkDir(new SVNURL[]{SVNURL.parseURIEncoded(repoRoot+"/"+dir)}, null);
							res =5;
						} else {
							res =7;
						}
					} else {
						for(int i=0; i<arrDir.length;i++ ) {
							location += "/" + arrDir[i];
							url += "/" + arrDir[i];
							if ( repo.checkPath("/"+location, -1) == SVNNodeKind.NONE ) {
								svnCommitClient.doMkDir(new SVNURL[]{SVNURL.parseURIEncoded(url)}, null);
								res =5;
							} else {
								res=7;	//整個for loop跑完， 最後若res為7，表示這整串都新增過了
							}
						}
					}
				}
			}
		}
		repo.closeSession();
		return res;
	}
	
	public int creJobInJenkins(String modId,String subsysId,String svnUrl,boolean isMR,boolean isAuth,String urlStr,String usr,String pwd) throws Exception {
		//修改 xml node中的 subversion
		final String jobName = (isMR?"MR":"OL")+"_"+subsysId+"_"+modId;
		final SAXReader reader = new SAXReader();
		final Document document = reader.read(  new FileInputStream(new File("D:/wsElegant/sysflow2/src/main/resources/createTemplate.xml")));
		
		//修改svn的位置
		document.getRootElement().element("scm").element("locations").element("hudson.scm.SubversionSCM_-ModuleLocation").element("remote").setText(svnUrl+modId);
		//修改maven的執行動作，取消原先預設的部署
		document.getRootElement().element("goals").setText("clean install");
		//遠端觸發的token
		document.getRootElement().element("authToken").setText(jobName);
		
		PostMethod postMethod = new PostMethod(urlStr+jobName);
		postMethod.setRequestHeader("Content-type","application/xml; charset=UTF-8");
		postMethod.setRequestBody(new FileInputStream( saveToFile(new File("tmp"+System.currentTimeMillis()+".xml"),document) ));
		
		final HttpClient client = new HttpClient();
		int returnCode = client.executeMethod(postMethod);
		return returnCode;
	}
	
	
	/**
	 *		取得目前時間格式的時間戳記
	 *		<br>創建時間：2013/9/24 上午10:41:30<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	private Timestamp currentTimeStamp(){
	    return Timestamp.valueOf( new SimpleDateFormat( dateFormat ).format( new Date() ) );
	}
	
	public void cwd (ChannelSftp sftp, String path) throws SftpException { 
		final String[] hie  = path.split("/");
		for(int i=0; i<hie.length;i++) {
			if ("".equalsIgnoreCase(hie[i])) {continue;}
			try {
				sftp.cd(hie[i]);
			} catch (SftpException e) {
				sftp.mkdir(hie[i]);
				sftp.cd(hie[i]);
			}
		}
	}
	
	public void cwdButNotmkdir (ChannelSftp sftp, String path) throws SftpException {
		
		String[] hie  = path.split("/");
		for(int i=0; i<hie.length;i++) {
			if ("".equalsIgnoreCase(hie[i])) {continue;}
			sftp.cd(hie[i]);
			System.out.println(sftp.pwd());
		}
	}
	
	
	/**
	 * 	將DB來的字串，其編碼轉成UI的編碼
	 *	<br>創建時間：2013/4/8 下午5:36:48
	 *	<br>創建人員：郭啟揚 (842297)
	 */
	public String DB2UI( String str ) throws UnsupportedEncodingException {
		if ( isCodingEqual ) return str;
		else return ( new String( str.getBytes( DBcodeSet ), UIcodeSet ) );	
	}
	
	
	/**
	 *		記錄錯誤記息
	 *		<br> @param e 錯誤的Exception
	 *		<br>創建時間：2013/9/24 上午10:47:23<br>
	 *		創建人員：郭啟揚 (842297)
	 * @return 
	 * @throws Exception 
	 */
	public String errLog(Exception e) {
		final String mes = errProcess(e);
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs= null;
		
		out.println(mes);
		try {
			conn = getConn();
			ps = conn.prepareStatement("INSERT INTO errmes (errMes,createdDateTime) VALUES (?,?)");
			ps.setString(1, UI2DB(mes) );
			ps.setTimestamp(2, currentTimeStamp() );
			ps.execute();
		} catch (Exception e1) {
			out.println( errProcess(e1) );
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e1) {
				out.println( errProcess(e1) );
			}
		}
		return mes;
	}
	
	/**
	 *		記錄錯誤記息
	 *		<br> @param e 錯誤的Exception
	 *		<br> @param des 相關的參數
	 *		<br>創建時間：2013/9/24 上午10:35:05<br>
	 *		創建人員：郭啟揚 (842297)
	 * @return 
	 * @throws Exception 
	 */
	public String errLog(Exception e,String des) {
		final String mes = errProcess(e);
		e.printStackTrace();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs= null;
		try {
			conn = getConn();
			ps = conn.prepareStatement("INSERT INTO errmes (errMes,des,createdDateTime) VALUES (?,?,?)");
			ps.setString(1, UI2DB(mes) );
			ps.setString(2, UI2DB(des) );
			ps.setTimestamp(3, currentTimeStamp() );
			ps.execute();
		} catch (Exception e1) {
			out.println( errProcess(e1) );
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e1) {
				out.println( errProcess(e1) );
			}
		}
		return mes;
	}
	
	/**
	 *		將errorMessage 改成我想看的格式字串
	 *		<br>創建時間：2013/9/24 上午10:32:12<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public String errProcess (Exception e) {
		String mes = e.toString() + "\n\n";
		for (int i=0; i<e.getStackTrace().length;i++) {
			mes += "  File Name:"+e.getStackTrace()[i].getFileName() + "\n";
			mes += "Line Number:"+e.getStackTrace()[i].getLineNumber() + "\n";
		}
		return mes;
	}
	
	public String errProcess4HTML (Exception e) {
		String mes = e.toString() + "<br><br>";
		for (int i=0; i<e.getStackTrace().length;i++) {
			mes += "  File Name:"+e.getStackTrace()[i].getFileName() + "<br>";
			mes += "Line Number:"+e.getStackTrace()[i].getLineNumber() + "<br>";
		}
		return mes;
	}
	
	/** 產生待辦事項
	 * @param subsysId	子系統ID
	 * @param masterId	主檔ID
	 * @param empId		發起這件事的人的ID
	 * @param dataType	待辦事項的型態
	 * @param roleId	角色類型
	 */
	public int genToDoList(String subsysId,String masterId,String empId,int dataType, int roleId ) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int toDoListId =0;
		try {
			conn = getConn();
			final String sql="INSERT INTO todolist (subsysId,dataType,masterId,roleId,cDT,creatorId) VALUES (?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS );
			ps.setString(1, subsysId );
			ps.setInt(2, dataType);				//主檔類型，1表示這個ID是來自變更申請
			ps.setString(3, masterId);			//主檔ID
			ps.setInt(4, roleId);				//角色ID
			ps.setTimestamp(5, cDT());
			ps.setString(6, empId);
			ps.executeUpdate();
			
			rs = ps.getGeneratedKeys();
			rs.next();
			toDoListId = rs.getInt(1);
			System.out.println("回傳時的toDoListId:"+toDoListId);
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( rs !=null ) { rs.close(); }
				if ( ps !=null ) { ps.close(); }
				if ( conn !=null ) { conn.close(); }	
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return toDoListId;
	}
	
	/**
	 * 取回指定子系統，在 Jenkins上的所有jobs
	 *	@param subsysId
	 *	@return
	 *	@throws Exception	
	 *	<br>創建時間：2013/10/22 下午2:09:21<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public ArrayList<String> getAllJobOnJenkins(String subsysId) throws Exception {
		final ArrayList<String> jbs = new ArrayList<String>();
		
		final Map<String,Object> map = new HashMap<String,Object>();
        InputStream res = null ;
        // 連線取得資料
        res = URLConnectionUtil.doGet(JENKINSURL+"api/xml",false,"","",map);
        final SAXReader reader = new SAXReader();
        final Document document = reader.read(res);
        final Element root = document.getRootElement();

        final Iterator<Element> it = root.elementIterator();

		while (it.hasNext()) {
			Element e = it.next();
			
			if ("job".equalsIgnoreCase( e.getName() ) ) {
				if ( e.element("name").getText().contains( "_"+subsysId+"_" ) ) {
					jbs.add( e.element("name").getText() );	
				}
			}
		}
		
		res.close();
		
		return jbs;
	}
	
	
	/** 查詢指定子系統下的 AP server 連線資訊
	 * @param subsysId
	 * @param apType	1 測試 2 AP 3 CM 4 其它 5 公用
	 * @return
	 * @throws Exception
	 */
	public ArrayList<S4> getAP4SubsysConnInfo(String subsysId, int apType) throws Exception {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		final ArrayList<S4> aps = new ArrayList<S4>();
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT apIP,apPORT,apUSR,apPWD FROM MPsubsys2APserver WHERE subsysId=? AND apType=?");
			ps.setString(1, subsysId);
			ps.setInt(2, apType);
			rs = ps.executeQuery();
			while ( rs.next() ) {
				final S4 ap = new S4();
				ap.setS1( rs.getString("apIP") );
				ap.setS2( rs.getString("apPORT") );
				ap.setS3( rs.getString("apUSR") );
				ap.setS4( rs.getString("apPWD") );
				aps.add(ap);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return aps;
	}
	
	
	/**
	 *		取得本機SVN的連線資訊
	 *		<br>創建時間：2013/9/30 上午10:24:42<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public ArrayList<String> getBaseSVNconnInfo(String subsysId, boolean isMR) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		final ArrayList<String> connSVNInfo = new ArrayList<String>();
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT svnUrl,svnUsr,svnPwd FROM MPsubsys2svn WHERE isCM='t' AND subsysId=?  AND isMR=?");
			ps.setString(1, subsysId);
			ps.setString(2, isMR?"t":"f");
			rs = ps.executeQuery();
			rs.next();
			
			connSVNInfo.add( DB2UI(rs.getString("svnUrl")) );
			connSVNInfo.add( DB2UI(rs.getString("svnUsr")) );
			connSVNInfo.add( DB2UI(rs.getString("svnPwd")) );
			
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return connSVNInfo;
	}
	
	/**
	 * 取得資料庫連線
	 */
	public Connection getConn() {
		Connection conn = null;

		try {
			final Context ctx = new InitialContext();
			final DataSource ds = (DataSource) ctx.lookup( JNDINAME );
			conn = ds.getConnection();
		} catch (Exception e) {
			out.println("建立連線時發生錯誤"+e.toString());
		}
		return conn;
	}
	
	/**	取得指定table目前的流水號
	 * @param table
	 * @return
	 */
	public int getCurrentSN(String table) {
		String sql = "UPDATE envtable_seq SET seqcurrent = (seqcurrent + 1) WHERE seqname=?";
		int res = 0;
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, table);
			ps.execute();
			
			sql = "SELECT seqcurrent FROM envtable_seq WHERE seqname=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, table);
			rs = ps.executeQuery();
			rs.next();
			res = rs.getInt(1);
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) { e.printStackTrace(); }
		}
		
		return res;
	}
	

	public int getDepSN(long depId) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int res=0;
		
		final String sql="SELECT COUNT(depMemphisSN),depMemphisSN FROM depmemphis WHERE depMemphisId=?";
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, depId);
			rs = ps.executeQuery();
			rs.next();
			if( rs.getInt(1) != 0){
				res=rs.getInt("depMemphisSN");
			} else {
				res=-1;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return res;
	}
	
	/**
	 * 取得指定員工的Email Addr，陣列0是名字，陣列1是email addr
	 *	@param empId
	 *	@return
	 *	@throws Exception	
	 *	<br>創建時間：2013/10/8 下午1:40:07<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public String[] getEmailAddr(String empId) throws Exception {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		final String[] emailAddr = new String[2];
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT empName,empEmail FROM emps WHERE empId=?");
			ps.setString(1, empId);
			rs = ps.executeQuery();
			rs.next();
			emailAddr[0] = rs.getString("empName");
			emailAddr[1] = rs.getString("empEmail");
			
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return emailAddr;
	}
	
	/**
	 *		回傳指定子系統下的所有人員
	 *		<br>創建時間：2013/9/30 下午3:26:25<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public ArrayList<S5> getEmpList(String subsysId) throws Exception {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		final ArrayList<S5> alOfS5 = new ArrayList<S5>();
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT DISTINCT t1.empId,t2.empName,t2.empEmail,t2.empPhone,t2.empPwd FROM mpemprolesubsys t1,emps t2 WHERE t1.empId=t2.empId AND t1.subsysId=?");
			ps.setString(1, subsysId);
			rs = ps.executeQuery();
			while (rs.next()) {
				final S5 s5 = new S5();
				s5.setS1( rs.getString("empId") );
				s5.setS2( rs.getString("empName") );
				s5.setS3( rs.getString("empPhone") );
				s5.setS4( rs.getString("empEmail") );
				s5.setS5( rs.getString("empPwd") );
				alOfS5.add(s5);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return alOfS5;
	
	}
	
	/**
	 * 給員工ID  來查員工姓名
	 *	@param empId	員工ID
	 *	@return
	 *	@throws Exception	
	 *	<br>創建時間：2013/10/8 下午3:11:57<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public String getEmpName(String empId) throws Exception {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String empName="";
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT empName FROM emps WHERE empId=?");
			ps.setString(1, empId);
			rs = ps.executeQuery();
			rs.next();
			empName = rs.getString("empName");
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return empName;
	}
	
	
	public String getEncap(String subsysId,String modId) throws Exception {
		String encapName = "";
		
		final ArrayList<String> connSVNInfo = getBaseSVNconnInfo(subsysId,false);	//先檢查OL
		final Map<String, Object> map = new HashMap<String, Object>();
		InputStream res = null;

		try {
			res = URLConnectionUtil.doGet(connSVNInfo.get(0)+modId+"/pom.xml", true, connSVNInfo.get(1),connSVNInfo.get(2), map);

			final SAXReader reader = new SAXReader();
			final Document document = reader.read(res);
			final Element root = document.getRootElement();

			final Iterator<Element> it = root.elementIterator();
			String fileName = "";
			String extName = "";
			while (it.hasNext()) {
				Element e = it.next();
				if ("name".equalsIgnoreCase(e.getName())) {
					fileName = e.getText();
				} else if ("packaging".equalsIgnoreCase(e.getName())) {
					extName = e.getText();
				}
				
				if (!"".equalsIgnoreCase(fileName) && !"".equalsIgnoreCase(extName)) {
					encapName = fileName + "." + extName;
					break;
				}
			}
		} catch (FileNotFoundException e) {
			out.println("該夾內不存在pom.xml");
		} finally {
			if (res !=null)res.close();
		}

		if ("".equalsIgnoreCase(encapName)) {
			out.println("POM沒有所要內容");
		}
		out.println("實際的值是：" + encapName);
		
		return encapName;
	}
	
	/**	取得指定部署執行下的部署細項號
	 * @param depmainSN
	 * @return
	 */
	public int getfDepdetailid(int depmainSN) {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int fdepdetailid =0;
		try {
			conn = getConn();
			String sql = "SELECT COUNT(*) FROM fdepdetail WHERE depmainSN=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, depmainSN);
			rs = ps.executeQuery();
			rs.next();
			fdepdetailid = rs.getInt(1)+1;
		} catch (Exception x) {
			errLog(x);
		} finally {
			try {
				if ( rs !=null ) { rs.close(); }
				if ( ps !=null ) { ps.close(); }
				if ( conn !=null ) { conn.close(); }	
			} catch (Exception e) { e.printStackTrace(); }
		}
		return fdepdetailid;
	}

	public String getJenkinsJobResult(String jobName,int jobSN,String infoName) throws Exception {
		String res = "";
		System.out.println("開始取得執行結果");
		final String urlStr = JENKINSURL+"job/"+jobName+"/"+jobSN+"/api/xml";
		final Map<String,Object> map = new HashMap<String,Object>();
		final InputStream ins = URLConnectionUtil.doGet(urlStr,false,JENKINS_USR,JENKINS_PWD,map);
		final SAXReader reader = new SAXReader();
		final Document document = reader.read(ins);
		ins.close();
		
		final Element root = document.getRootElement();
		Iterator<Element> it = root.elementIterator();
		int i=0;
		if ( "result".equalsIgnoreCase(infoName) ) {
			boolean isNotExist= true;
			while ( isNotExist ) {
				it = root.elementIterator();
				while(it.hasNext()) {
					i++;
					final Element e = it.next();
					System.out.println("目前的節點："+e.getName());
					if( infoName.equalsIgnoreCase(e.getName()) ){
						isNotExist = false;
						res = e.getText();
					}
					
					System.out.println("執行的次數："+i);
					if( i>1000 ) {
						System.out.println("失敗了");
						return "FAILURE";
					}
				}
				if ( isNotExist ) {
					for(long a=0; a<999999999;a++) {}
				}
			}
		} else {
			while(it.hasNext()) {
				final Element e = it.next();
				if( infoName.equalsIgnoreCase(e.getName()) ){
					res = e.getText();
				}
			}
		}
    	
		return res;
	}
	
	public int getLastBuildNumber(String jobName) throws IOException {
		int lastBuildNumber = 0;
		final String urlStr = JENKINSURL+"job/"+jobName+"/api/xml";
		out.print("開始查詢"+jobName+"的最後執行版次...結果是：");
		// 連線取得資料
        InputStream ins = null; 
        final SAXReader reader = new SAXReader();
        final Map<String,Object> map = new HashMap<String,Object>();
        try {
        	ins = URLConnectionUtil.doGet(urlStr,false,JENKINS_USR,JENKINS_PWD,map);
        	final Document document = reader.read(ins);
        	ins.close();
        	final Element root = document.getRootElement();
    		Iterator<Element> it = root.elementIterator();	
        	while (it.hasNext()) {
    			Element e2 = it.next();
    			if ( "nextBuildNumber".equalsIgnoreCase(e2.getName() ) ) {
    				lastBuildNumber = Integer.parseInt(e2.getText())-1;
    			}
    		}
        } catch (Exception e) {
        	e.printStackTrace();
        }
        
        out.println(lastBuildNumber);
        return lastBuildNumber;
	}
	
	public int getLatestDepMemphisDetailId(int depMemphisSN) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int res=0;
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT COUNT(depMemphisDetailId) FROM depmemphisdetail WHERE depMemphisSN=?");
			ps.setLong(1, depMemphisSN);
			rs = ps.executeQuery();
			rs.next();
			if ( rs.getInt(1)==0 ) {
				res=1;	
			} else {
				res= rs.getInt(1)+1;
			}
			
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return res;
	}
	
	
	/**	取得特定檔案在某 Server Group上，最後部署的版本號
	 *	@param squadId
	 *	@param fileName
	 *	@return
	 *	@throws Exception	
	 *	<br>創建時間：2013/11/13 下午8:29:10<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public int getLatestDepVer(String squadId,String fileName) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int res=0;
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT t2.fileVer FROM depmemphis t1, depmemphisdetail t2 WHERE t1.depMemphisSN=t2.depMemphisSN AND t2.squadId=? AND t2.fileName=? AND t2.isAssume='f' ORDER BY t1.cDT DESC");
			ps.setString(1, squadId);
			ps.setString(2, fileName);
			rs = ps.executeQuery();
			if ( rs.next() ) {
				res= rs.getInt(1);	
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return res;
	}
	
	/**
	 *		取得本系統在系統註冊裡的ID號
	 *		<br>創建時間：2013/9/30 上午10:29:27<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public int getLocalSysemId() throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int sysSN =0;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT sysSN FROM envsystemlist WHERE isLocalSys='t'");
			rs = ps.executeQuery();
			rs.next();
			sysSN = rs.getInt("sysSN");
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return sysSN;
	}
	
	
	/**	取得本系統在系統註冊裡的名稱
	 * @return
	 * @throws Exception 
	 * @throws SQLException
	 * @throws UnsupportedEncodingException
	 */
	public String getLocalSysName() throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sysName ="";
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT sysname FROM envsystemlist WHERE isLocalSys='t'");
			rs = ps.executeQuery();
			rs.next();
			sysName = rs.getString("sysname");
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		return sysName;
	}
	
	/**
	 * 	取得指定子系統下指定角色的人員寄信清單
	 *	@param subsysId	那個那系統
	 *	@param roleId	那個角色
	 *	@return	
	 *	<br>創建時間：2013/10/8 下午1:27:04<br>
	 *	創建人員：郭啟揚 (842297)
	 * @throws Exception 
	 */
	public ArrayList<String[]> getMailList(String subsysId,int roleId) throws Exception {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		final ArrayList<String[]> mailList = new ArrayList<String[]>();
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT t2.empName,t2.empEmail FROM mpemprolesubsys t1,emps t2 WHERE t1.empId=t2.empId AND t1.subsysId=? AND t1.roleId=?");
			ps.setString(1, subsysId);
			ps.setInt(2, roleId);
			rs = ps.executeQuery();
			while (rs.next()) {
				final String[] mailItem = new String[2];
				mailItem[0] = rs.getString("empName");
				mailItem[1] = rs.getString("empEmail");
				mailList.add(mailItem);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return mailList;
	}
	
	/**	取得指定檔案目前最大的版次數
	 * 
	 *	@param svnURL
	 *	@param relativePath
	 *	@param fileName
	 *	@return
	 *	@throws Exception	
	 *	<br>創建時間：2013/11/18 下午1:53:21<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public int getMAXfileVer(boolean isMR,String relativePath,String fileName ) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int fileVer =0;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT MAX(fileVer) FROM chkinmoddetail WHERE isMR=? AND relativePath=? AND fileName=?");
			ps.setString(1, isMR?"t":"f");
			ps.setString(2, relativePath);
			ps.setString(3, fileName);
			rs = ps.executeQuery();
			if (rs.next() ) { fileVer = rs.getInt(1); }
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return fileVer;
	}
	
	/**
	 * 	<isCascade尚未實作>查詢指定模組，在建置封裝時，其下游模組
	 *	@param modId	欲查詢的模組ID
	 *	@param isCascade	是否自此模組以降開始一連串的查詢每個節點
	 *	@param urlStr	jenkins的連結位置
	 *	@param usr		jenkins的登入帳號
	 *	@param pwd		jenkins的登入密碼
	 *	@return	查詢結果
	 *	<br>創建時間：2013/10/22 上午9:03:48<br>
	 *	創建人員：郭啟揚 (842297)
	 * @throws Exception 
	 */
	public ArrayList<ModNode> getModBEdependency(String modId,boolean isAuth, boolean isCascade,String urlStr,String usr,String pwd) throws Exception {
		final ArrayList<ModNode> mns = new ArrayList<ModNode>();
		
		final Map<String,Object> map = new HashMap<String,Object>();
        
        // 連線取得資料
        InputStream res = null; 
        final SAXReader reader = new SAXReader();
        
        try {
        	res = URLConnectionUtil.doGet(urlStr,isAuth,usr,pwd,map);
        	final Document document = reader.read(res);
    		final Element root = document.getRootElement();
    		final Iterator<Element> it = root.elementIterator();	
        	
        	while (it.hasNext()) {
    			final Element e = it.next();
    			if ( "publishers".equalsIgnoreCase(e.getName() ) ){
    				if( e.elementIterator().hasNext() ){
    					final String tmpStr = e.element("hudson.tasks.BuildTrigger").element("childProjects").getText();
    					final String[] downStreamModId = tmpStr.split(",");	//因為可能下游不只一個
    					
    					for(int i=0;i<downStreamModId.length;i++){
    						final ModNode mn = new ModNode();
    						mn.setCurrentNode(modId);
    						mn.setDownSreamNode( downStreamModId[i] );
    						mns.add(mn);
    					}
    				}
    			}
    		}
        } catch (Exception e) {
        	errLog(e);
        	throw e;
        } finally {
        	res.close();
        }
		
		return mns;
	}
	
	
	public ArrayList<S1AS2> getModBEdependencyS1AS2(String modId,boolean isAuth, boolean isCascade,String urlStr,String usr,String pwd) throws Exception {
		final ArrayList<S1AS2> mns = new ArrayList<S1AS2>();
		
		final Map<String,Object> map = new HashMap<String,Object>();
		String tmpStr = "";
        // 連線取得資料
        InputStream res = null; 
        final SAXReader reader = new SAXReader();
        try {
        	res = URLConnectionUtil.doGet(urlStr,isAuth,usr,pwd,map);
        	final Document document = reader.read(res);
    		final Element root = document.getRootElement();
    		Iterator<Element> it = root.elementIterator();
    		
    		while( it.hasNext() ) {
    			Element e = it.next();
    			if( "publishers".equalsIgnoreCase( e.getName() ) ) {
    				it = e.elementIterator();
    				while( it.hasNext() ) {
    					e = it.next();
    					if( "hudson.tasks.BuildTrigger".equalsIgnoreCase( e.getName() ) ) {
    						it = e.elementIterator();
    						while( it.hasNext() ) {
    							e = it.next();
    							if( "childProjects".equalsIgnoreCase( e.getName() ) ) {
    								tmpStr = e.getText();
    	    						final String[] downStreamModId = tmpStr.split(",");	//因為可能下游不只一個
    	    						
    	    						for(int i=0;i<downStreamModId.length;i++){
    	    							final S1AS2 tmp = new S1AS2();
    	    							tmp.setJobName( downStreamModId[i] );
    	    							mns.add(tmp);
    	    						}    								
    							}
    						}
    					}
    				}
    			}
    		}
    		
			
        } catch (Exception e) {
        	errLog(e);
        	throw e;
        } finally {
        	res.close();
        }
		
		return mns;
	}
	
	public ArrayList<String> getModBEdependencyString(String modId,boolean isAuth, boolean isCascade,String urlStr,String usr,String pwd) throws Exception {
		final ArrayList<String> mns = new ArrayList<String>();
		
		final Map<String,Object> map = new HashMap<String,Object>();
        
        // 連線取得資料
        InputStream res = null; 
        final SAXReader reader = new SAXReader();
        
        try {
        	res = URLConnectionUtil.doGet(urlStr,isAuth,usr,pwd,map);
        	final Document document = reader.read(res);
    		final Element root = document.getRootElement();
    		
    		Iterator<Element> it = root.elementIterator();
    		
    		while( it.hasNext() ) {
    			Element e = it.next();
    			if( "publishers".equalsIgnoreCase( e.getName() ) ) {
    				it = e.elementIterator();
    				while( it.hasNext() ) {
    					e = it.next();
    					if( "hudson.tasks.BuildTrigger".equalsIgnoreCase( e.getName() ) ) {
    						it = e.elementIterator();
    						while( it.hasNext() ) {
    							e = it.next();
    							if( "childProjects".equalsIgnoreCase( e.getName() ) ) {
    								final String tmpStr = e.getText();
    	    						final String[] downStreamModId = tmpStr.split(",");	//因為可能下游不只一個
    	    						
    	    		    			for(int i=0;i<downStreamModId.length;i++){
    	    		    				mns.add(downStreamModId[i]);
    	    		    			}    								
    							}
    						}
    					}
    				}
    			}
    		}
    		
        } catch (Exception e) {
        	errLog(e);
        	throw e;
        } finally {
        	res.close();
        }
		
		return mns;
	}
	
	public String getModBEdependencyString2(boolean isAuth, boolean isCascade,String urlStr,String usr,String pwd) throws Exception {
		
		final Map<String,Object> map = new HashMap<String,Object>();
        String tmpStr = "";
        // 連線取得資料
        InputStream res = null; 
        final SAXReader reader = new SAXReader();
        
        try {
        	res = URLConnectionUtil.doGet(urlStr,isAuth,usr,pwd,map);
        	final Document document = reader.read(res);
    		final Element root = document.getRootElement();
    		final Iterator<Element> it = root.elementIterator();	
        	
        	while (it.hasNext()) {
    			final Element e = it.next();
    			if ( "publishers".equalsIgnoreCase(e.getName() ) ){
    				if( e.elementIterator().hasNext() ){
    					tmpStr = e.element("hudson.tasks.BuildTrigger").element("childProjects").getText().trim();
    				}
    			}
    		}
        } catch (Exception e) {
        	errLog(e);
        	throw e;
        } finally {
        	res.close();
        }
		
		return tmpStr;
	}
	
	/**	連線到指定的SVN的模組夾中，查詢它的pom檔，辨別其封裝檔的副檔名
	 * @param url
	 * @param usr
	 * @param pwd
	 * @param modId
	 * @return
	 * @throws Exception
	 */
	public String getModExt( String url,String usr,String pwd,String modId) throws Exception {
		String res = "";
		String version = "";
		String ext = "";
		final SVNRepository repo = SVNRepositoryFactory.create(SVNURL.parseURIEncoded(url));
		final ISVNAuthenticationManager authManager = SVNWCUtil.createDefaultAuthenticationManager(usr,pwd);
		repo.setAuthenticationManager(authManager);
		final ByteArrayOutputStream baos = new ByteArrayOutputStream();
		
		final SAXReader reader = new SAXReader();
		
		final SVNProperties fileProperties = new SVNProperties();
		repo.getFile( modId+"/pom.xml",-1, fileProperties, baos);
		baos.close();
		final InputStream in = new ByteArrayInputStream(baos.toByteArray() );
		final Document document = reader.read(in);
		final Element root = document.getRootElement();
		
		Iterator<Element> it = root.elementIterator();	
    	while (it.hasNext()) {
			final Element e = it.next();
			if ( "version".equalsIgnoreCase(e.getName() ) ){
				version = e.getText();
			}
		}
    	
    	it = root.elementIterator();
    	while (it.hasNext()) {
			final Element e = it.next();
			if ( "packaging".equalsIgnoreCase(e.getName() ) ){
				ext = e.getText();
			}
		}
    	in.close();
		repo.closeSession();
		
		if( "".equalsIgnoreCase(version) ){
			res = "."+ext;
		} else {
			res = "-"+version+"."+ext;
		}
		
		
		return res;
	}
	
	/**
	 * 查詢模組，與其預計產生的封裝檔
	 *	@param subsysId
	 *	@return
	 *	@throws Exception	
	 *	<br>創建時間：2013/10/30 下午4:29:59<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public ArrayList<S2> getModUnitEncap(String subsysId) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		final ArrayList<S2> modEncaps = new ArrayList<S2>();
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT modUnitId FROM modUnits WHERE subsysId=? ORDER BY modUnitId");
			ps.setString(1, subsysId);
			rs = ps.executeQuery();
			while (rs.next()) {
				final S2 modEncap = new S2();
				modEncap.setS1(rs.getString("modUnitId"));
				modEncap.setS2( getEncap(subsysId,rs.getString("modUnitId")) );
				modEncaps.add(modEncap);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		return modEncaps;
	}
	
	/**
	 * 查詢其指定子系統下的所有模組
	 *	@param subsysId
	 *	@return
	 *	@throws Exception	
	 *	<br>創建時間：2013/10/9 下午1:44:20<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public ArrayList<String> getModUnits(String subsysId) throws Exception {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		final ArrayList<String> modUnits = new ArrayList<String>();
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT modUnitId FROM modUnits WHERE subsysId=? ORDER BY modUnitId");
			ps.setString(1, subsysId);
			rs = ps.executeQuery();
			while (rs.next()) {
				modUnits.add(rs.getString("modUnitId"));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		return modUnits;
	}
	
	public int getParNumberFromRequest(String str) {
		int res=0;
		if ( isReqExist(str) ) {
			if( isNumaric(str) ) {
				res = Integer.parseInt(str);
			}
		}
		return res;
	}
	
	
	/**
	 * 	讀取Profile的資訊，供前端選擇
	 *	@param subsysId	子系統ID
	 *	@return 
	 *	@throws Exception	
	 *	<br>創建時間：2013/10/3 上午9:47:24<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public ArrayList<S3> getPf(String subsysId) throws Exception {
		final String sql = "SELECT pfId,jScnarioID,pfName FROM profiles WHERE subsysId=?";
		final ArrayList<S3> pfsINsubsys = new ArrayList<S3>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement( sql );
			ps.setString(1, subsysId);
			rs = ps.executeQuery();
			while ( rs.next() ) {
				final S3 pfInfo = new S3();
				pfInfo.setS1( rs.getString("pfName") );					//Profile 名稱
				pfInfo.setS2( String.valueOf(rs.getInt("pfId")) );			//Profile ID
				pfInfo.setS3( rs.getString("jScnarioID") );	//jenkins設定ID
				pfsINsubsys.add( pfInfo );
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return pfsINsubsys;	
	}
	
	public ArrayList<QDPS> getQDR4Paris() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		
		final ArrayList<QDPS> qdps = new ArrayList<QDPS>();
		
		
		try {
			conn = getConn();
			
			// 查詢 fDepdetail 確認該次部署到底部署了那些Squad(因為Squad的設定可能會變，所以不能直接讀當前的squad)
			//查詢所有部署記錄
			String sql = "SELECT t1.depmainSN,t3.deliverySN,t1.isUrgent,t5.envId,t5.envName,t4.empName,t1.createdDateTime,t2.deplmainSN FROM depmain t1,deplmain t2, deltask t3,emps t4,env t5 WHERE t1.subsysId=t5.subsysId AND t1.envId=t5.envId AND t1.creator=t4.empId AND t1.deplmainSN=t2.deplmainSN AND t2.deplmainSN=t3.deplmainSN ORDER BY depmainSN DESC";			
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while( rs.next() ) {
				final QDPS qdp = new QDPS();
				int rowsnum = 0;
				final ArrayList<SquadParis> sps = new ArrayList<SquadParis>();	//這次部署牽涉那些Server Group
				qdp.setSps(sps);
				final ArrayList<FileStr> files = new ArrayList<FileStr>();	//這次部署那些檔案
				final ArrayList<Req> reqs = new ArrayList<Req>();			//包含那些需求
				
				sql = "SELECT DISTINCT t1.reqSID,t1.reqDESC FROM reqs t1,mpreqfiles t2 WHERE t1.reqId=t2.reqSN AND t2.deplmainSN=?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, rs.getInt("deplmainSN") );
				rs2 = ps.executeQuery();
				while( rs2.next() ) {
					final Req req = new Req();
					req.setReqSID( rs2.getString("reqSID") );
					req.setReqDesc( rs2.getString("reqDesc") );
					reqs.add(req);
				}
				qdp.setReqs(reqs);
				qdp.setDepmainSN( rs.getInt("depmainSN") );
				qdp.setDeliverySN( rs.getInt("deliverySN") );
				qdp.setUrgent( "t".equalsIgnoreCase( rs.getString("isUrgent") ) );
				qdp.setEnvId( rs.getString("envId") );
				qdp.setEnvName( rs.getString("envName") );
				qdp.setEmpName( rs.getString("empName") );
				qdp.setbDT( changeTimeFormatUI( rs.getTimestamp("createdDateTime") ) );
				
				sql = "SELECT DISTINCT t2.localFile,t2.localVer,t2.fileSizeInByte FROM depmain t1,depldetail t2 WHERE t1.depmainSN=? AND t1.deplmainSN=t2.deplmainSN ORDER BY localFile DESC";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, qdp.getDepmainSN() );
				rs2 = ps.executeQuery();
				while( rs2.next() ) {
					final FileStr file = new FileStr();
					file.setFileName( rs2.getString("localFile") );		//檔名
					file.setLocalVer( rs2.getInt("localVer") );			//版次
					file.setFileSize( rs2.getInt("fileSizeInByte") );	//檔案大小
					files.add(file);
				}
				qdp.setFiles(files);
				
				//查詢在該次部署中，部署了那些Server Group
				sql = "SELECT DISTINCT squadId FROM fdepDetail WHERE depmainSN=? AND squadId IS NOT NULL";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, qdp.getDepmainSN() );
				rs2 = ps.executeQuery();
				while( rs2.next() ) {
					final SquadParis sp = new SquadParis();
					final String squadId = rs2.getString("squadId");
					sp.setSquadId( squadId );
					final ArrayList<FileStr> filesInSP = new ArrayList<FileStr>();	//這次部署那些檔案
					for(int i=0;i<files.size();i++) {
						final FileStr file = new FileStr();
						file.setFileName( files.get(i).getFileName() );		//檔名
						file.setLocalVer( files.get(i).getLocalVer() );		//版次
						file.setFileSize( files.get(i).getFileSize() );		//檔案大小
						filesInSP.add(file);
					}
					for (int i=0;i<filesInSP.size();i++) {
						final S8 s8 = new S8();
						
						sql = "SELECT bDT,eDT,depResult FROM fdepdetail WHERE depmainSN=? AND fDepActTypeId=5 AND depFN=? AND squadId=?";
						ps = conn.prepareStatement(sql);
						ps.setInt(1, qdp.getDepmainSN() );
						ps.setString(2, filesInSP.get(i).getFileName() );
						ps.setString(3, squadId );
						rs3 = ps.executeQuery();
						if ( rs3.next() ) {	//表示這次部署，這個Server Group上，有對這支檔案進行下線
							s8.setS3( stringFilter( rs3.getString("depResult") ) );
							s8.setS1( s8.getS3().indexOf("{\n    \"outcome\" => \"success\"")!=0?"失敗":"成功");
							s8.setS5( getTime( rs3.getTimestamp("bDT") ) );
							s8.setS7( getTime( rs3.getTimestamp("eDT") ) );
						} else {
							s8.setS1("未執行");
						}
						sql = "SELECT bDT,eDT,depResult FROM fdepdetail WHERE depmainSN=? AND fDepActTypeId=6 AND depFN=? AND squadId=?";
						ps = conn.prepareStatement(sql);
						ps.setInt(1, qdp.getDepmainSN() );
						ps.setString(2, filesInSP.get(i).getFileName() );
						ps.setString(3, squadId );
						rs3 = ps.executeQuery();
						if ( rs3.next() ) {	//表示這次部署，這個Server Group上，有對這支檔案進行部署
							s8.setS4( stringFilter( rs3.getString("depResult") ) );
							s8.setS2( s8.getS4().indexOf("{\n    \"outcome\" => \"success\"")==0?"成功":"失敗");
							s8.setS6( getTime( rs3.getTimestamp("bDT") ) );
							s8.setS8( getTime( rs3.getTimestamp("eDT") ) );
						} else {
							s8.setS2("未執行");
						}
						filesInSP.get(i).setDepRes( s8 );
					}
					sp.setFiles(filesInSP);	//ServerGroup下的檔案已處理完成

					final ArrayList<S5> hts = new ArrayList<S5>();	//s1 ip名稱,s2關閉Apache的執行狀況,s3開啟Apache的執行狀況
					
					sql = "SELECT DISTINCT hostIP FROM fdepdetail WHERE depmainSN=? AND hostIP IS NOT NULL AND squadId=?";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, qdp.getDepmainSN() );
					ps.setString(2, squadId );
					rs3 = ps.executeQuery();
					while( rs3.next() ) {
						final S5 ht = new S5();
						ht.setS1( rs3.getString("hostIP") );
						hts.add(ht);
					}
					sp.setHts(hts);					
					
					sql = "SELECT fdepActTypeId,hostIP,depResult FROM fdepDetail WHERE depmainSN=? AND hostIP IS NOT NULL AND fDepActTypeId IN (3,7) AND squadId=?";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, qdp.getDepmainSN() );
					ps.setString(2, squadId );
					rs3 = ps.executeQuery();
					while( rs3.next() ) {
						if ( rs3.getInt("fdepActTypeId") == 3 ) {	//s2記錄關閉httpd Service成功否，s4是原始執行記錄
							for(int i=0;i<sp.getHts().size();i++) {	
								if( sp.getHts().get(i).getS1().equalsIgnoreCase(rs3.getString("hostIP")) ) {
									//s2記錄關閉的執行結果
									sp.getHts().get(i).setS2( rs3.getString("depResult").contains("httpd is stopped")?"成功":"失敗" );
									sp.getHts().get(i).setS4( rs3.getString("depResult") );
								}
							}
						} else {									//s3記錄開啟httpd Service成功否，s5是原始執行記錄
							for(int i=0;i<sp.getHts().size();i++) {
								if( sp.getHts().get(i).getS1().equalsIgnoreCase(rs3.getString("hostIP")) ) {
									//s3記錄開啟的執行結果
									sp.getHts().get(i).setS3( rs3.getString("depResult").contains("httpd (pid")?"成功":"失敗" );
									sp.getHts().get(i).setS5( rs3.getString("depResult") );
								}
							}
						}
					}
					rowsnum += (4+sp.getHts().size()*2+sp.getFiles().size()*2);
					qdp.getSps().add(sp);
				}
				qdp.setRowspan(rowsnum);
				qdps.add(qdp);
			}
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( rs !=null ) { rs.close(); }
				if ( rs2 !=null ) { rs2.close(); }
				if ( rs3 !=null ) { rs3.close(); }
				if ( ps !=null ) { ps.close(); }
				if ( conn !=null ) { conn.close(); }
			} catch (Exception e) { e.printStackTrace(); }
		}
		return qdps;
	}
	
	/**
	 *		查詢子系統下，尚未完成的需求
	 *		<br>創建時間：2013/9/27 上午10:14:26<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public ArrayList<S4> getReqs(String subsysId) throws Exception {
		final String sql = "SELECT t1.reqDesc,t1.reqId,t1.cDT,t2.empName FROM reqs t1,emps t2 WHERE t1.isClosed='f' AND t1.creatorId=t2.empId AND t1.subsysId=? ORDER BY t1.cDT DESC";
		final ArrayList<S4> reqsINsubsys = new ArrayList<S4>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, subsysId);
			rs = ps.executeQuery();
			while ( rs.next() ) {
				final S4 reqInfo = new S4();
				reqInfo.setS1( rs.getString("reqDesc") );				//需求描述
				reqInfo.setS2( String.valueOf(rs.getInt("reqId")) );			//需求ID
				reqInfo.setS3( changeTimeFormatUI(rs.getTimestamp("cDT")) );	//需求創建日期
				reqInfo.setS4( rs.getString("empName") );				//創建人員
				reqsINsubsys.add(reqInfo);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return reqsINsubsys;	
	}
	
	
	/**
	 *	@param subsysId
	 *	@param empId
	 *	@param ModId
	 *	@return
	 *	@throws Exception	
	 *	<br>創建時間：2013/10/28 下午3:38:42<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public ArrayList<S4> getReqsInCra(String subsysId,String empId,String modId) throws Exception {
		final String sql = "SELECT t5.reqId, t5.reqDesc,t5.cDT,t6.empName FROM reqs t5, emps t6 WHERE t5.creatorId=t6.empId AND t5.reqId IN (SELECT DISTINCT t4.reqId FROM mpcrareq t4 WHERE (t4.craId,t4.craDetId) IN ( SELECT DISTINCT t2.craId,t2.craDetId FROM mpcraperformers t1,cradetail t2,cramain t3 WHERE t2.modUnitId=? AND  t2.isOpen='t' AND t2.craId=t3.craId AND t1.craDetId=t2.craDetId AND t3.craStatusId=5 AND t1.craId=t2.craId AND ((t1.performerId in (SELECT t4.roleId FROM mpemprolesubsys t4 WHERE t4.subsysId=t3.subsysId AND t4.empId=?)  AND t1.isRole='t') OR (t1.performerId=? AND t1.isRole='f') )  )) ORDER BY t5.cDT DESC";
		final ArrayList<S4> reqsINsubsys = new ArrayList<S4>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, modId);
			ps.setString(2, empId);
			ps.setString(3, empId);
			rs = ps.executeQuery();
			while ( rs.next() ) {
				S4 reqInfo = new S4();
				reqInfo.setS1( rs.getString("reqDesc") );				//需求描述
				reqInfo.setS2( String.valueOf(rs.getInt("reqId")) );			//需求ID
				reqInfo.setS3( changeTimeFormatUI(rs.getTimestamp("cDT")) );	//需求創建日期
				reqInfo.setS4( rs.getString("empName") );				//創建人員
				reqsINsubsys.add(reqInfo);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return reqsINsubsys;	
	
	}
	
	
	/**
	 *		查詢有那些角色<br>
	 *		@param roleTypeId 將角色進行分類 
	 *		<br>創建時間：2013/9/30 下午2:30:27<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public ArrayList<I1S1> getRoleList(String roleTypeId) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		final ArrayList<I1S1> alOfI1S1 = new ArrayList<I1S1>();	// array list Of I1S1
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT roleId,roleDesc FROM roles WHERE roleTypeId=?");
			ps.setInt(1, Integer.parseInt(roleTypeId));
			rs = ps.executeQuery();
			while (rs.next()) {
				final I1S1 i1s1 = new I1S1();
				i1s1.setI1( rs.getInt("roleId") );
				i1s1.setS1( rs.getString("roleDesc") );	
				alOfI1S1.add(i1s1);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return alOfI1S1;
	}
	
	/**
	 *		查詢既有子系統的設定回傳，以供後續維護的顯示
	 *		<br>創建時間：2013/10/1 上午10:55:00<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public ArrayList<Subsys4Maintain> getsubsys4maintain() throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		final ArrayList<Subsys4Maintain> alOfSubsys4Maintain = new ArrayList<Subsys4Maintain>();	// array list Of Subsys4Maintain
		
		//查詢 子系統的SVN 設定
		final String sql = "SELECT t2.subsysId,t1.subsysDesc,t2.svnTypeId,t2.svnName,t2.svnUrl,t2.svnUsr,t2.svnPwd FROM subsyses t1,mpsubsys2svn t2 WHERE t1.subsysId=t2.subsysId AND t2.svnTypeId<5 ORDER BY t2.subsysId,t2.svnTypeId";
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			String preSubsysId = "";
			while (rs.next()) {
				
				/*
				 * s1 SVN 名稱
				 * s2 SVN Repo位址
				 * s3 SVN 登入帳號
				 * s4 SVN 登入密碼
				 * */
				final int sti = rs.getInt("svnTypeId");
				final S4 s4 = new S4();
				s4.setS1( rs.getString("svnName") );
				s4.setS2( rs.getString("svnUrl") );
				s4.setS3( rs.getString("svnUsr") );
				s4.setS4( rs.getString("svnPwd") );
				
				if ( rs.getString("subsysId").equalsIgnoreCase( preSubsysId ) ) {	//相等的話，表示仍屬同一個子系統的SVN設定
					final int idx = alOfSubsys4Maintain.size()-1;
					
					if ( sti == 1 ) {
						alOfSubsys4Maintain.get(idx).setCMMRok(true);
						alOfSubsys4Maintain.get(idx).getCm().setMRok(true);
						alOfSubsys4Maintain.get(idx).getCm().setS4MR(s4);
					} else if ( sti == 2 ) {
						alOfSubsys4Maintain.get(idx).setCMOLok(true);
						alOfSubsys4Maintain.get(idx).getCm().setOLok(true);
						alOfSubsys4Maintain.get(idx).getCm().setS4OL(s4);
					} else if ( sti == 3 ) {
						alOfSubsys4Maintain.get(idx).setAPMRok(true);
						alOfSubsys4Maintain.get(idx).getAp().setMRok(true);
						alOfSubsys4Maintain.get(idx).getAp().setS4MR(s4);
					} else if ( sti == 4 ) {
						alOfSubsys4Maintain.get(idx).setAPOLok(true);
						alOfSubsys4Maintain.get(idx).getAp().setMRok(true);
						alOfSubsys4Maintain.get(idx).getAp().setS4OL(s4);
					}
				} else {	//新的一個子系統
					final Subsys4Maintain s4m =new Subsys4Maintain();
					
					s4m.setSubsysId( rs.getString("subsysId") );
					s4m.setSubsysName( rs.getString("subsysDesc") );
					
					
					if ( sti == 1 ) {
						s4m.setCMMRok(true);
						s4m.getCm().setMRok(true);
						s4m.getCm().setS4MR(s4);
					} else if ( sti == 2 ) {
						s4m.setCMOLok(true);
						s4m.getCm().setOLok(true);
						s4m.getCm().setS4OL(s4);
					} else if ( sti == 3 ) {
						s4m.setAPMRok(true);
						s4m.getAp().setMRok(true);
						s4m.getAp().setS4MR(s4);
					} else if ( sti == 4 ) {
						s4m.setAPOLok(true);
						s4m.getAp().setOLok(true);
						s4m.getAp().setS4OL(s4);
					}
					
					
					alOfSubsys4Maintain.add(s4m);
				} // else的括弧結尾
					
				preSubsysId = rs.getString("subsysId");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return alOfSubsys4Maintain;
	}
	
	/**
	 *		給定員工ID 和 網頁名稱，查詢他在指定的網頁中，可以看到的子系統有那些
	 *
	 *		@return ResultSet 查詢的結果
	 *		<br>創建時間：2013/9/23 下午8:18:38<br>
	 *		創建人員：郭啟揚 (842297)
	 *		@throws Exception 
	 */
	public ArrayList<S2> getSubsysList(String empId, String pageName ) throws Exception {
		String sql = "SELECT COUNT(*) FROM mpemprolesubsys WHERE empId=? AND roleId=1"; 
		final ArrayList<S2> s4 = new ArrayList<S2>(); 	//因為subsyses 中有四個s
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, empId);
			rs = ps.executeQuery();
			rs.next();
			final boolean isSuperAdmin = rs.getInt(1)==1;
			
			if ( isSuperAdmin ) {
				sql = "SELECT subsysId,subsysDesc FROM subsyses";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
			} else {
				sql = "SELECT t1.subsysId,t1.subsysDesc FROM subsyses t1,pageList t2,mprolepagelist t3,mpemprolesubsys t4 WHERE t4.roleId=t3.roleId AND t1.subsysId=t4.subsysId AND t4.empId=? AND t3.pageId=t2.pageId AND t2.pageUrl=? ORDER BY t1.subsysId";
				ps = conn.prepareStatement(sql);
				ps.setString(1, empId);
				ps.setString(2, pageName);
				rs = ps.executeQuery();
			}
			while ( rs.next() ) {
				final S2 s2 = new S2();
				s2.setS1( rs.getString("subsysId") );
				s2.setS2( rs.getString("subsysDesc") );
				s4.add(s2);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return s4;		
	}
	
	public String getSVN2depFileTreeJSON(String url,String usr,String pwd) {
		DAVRepositoryFactory.setup();
		
		SVNClientManager manager;
		SVNURL repositoryBaseUrl;
		
		final DefaultSVNOptions options = new DefaultSVNOptions();
		manager = SVNClientManager.newInstance(options,usr,pwd); // 如果需要用戶名密碼
		try {
			repositoryBaseUrl = SVNURL.parseURIEncoded(url); // 傳入svn地址
			final SVNLogClient logClient = manager.getLogClient();
			final DepEntryHandler handler = new DepEntryHandler(); // 在svn
			logClient.doList(repositoryBaseUrl, SVNRevision.HEAD, SVNRevision.HEAD,false, true, handler); // 列出當前svn地址的目錄，對每個文件進行處理
			res = res.substring(0, res.length()-1) + "]";
			
			if (!"]".equalsIgnoreCase(res)) {
				final ArrayList<DepFileNode> depFileNodes = new Gson().fromJson(res, new TypeToken<List<DepFileNode>>(){}.getType());
				for(int i=0;i<depFileNodes.size();i++) {
					if( depFileNodes.get(i).getVerSets() != null ) {
						ArrayList<Long> verSets = chkFileRevision(url+depFileNodes.get(i).getId(),usr,pwd );
						String vs="";
						for(int j=0;j<verSets.size();j++) {
							vs += verSets.get(j)+TOKENONE;
						}
						vs = vs.substring(0, vs.length()-TOKENONE.length());
						depFileNodes.get(i).setVerSets(vs);
					}
				}
				res = new GsonBuilder().disableHtmlEscaping().create().toJson( depFileNodes ).replaceAll("\"", "'");
			}
		} catch (SVNException e) {
			errLog(e);
		}
		
		return res;
	}
	
	/**
	 *		回傳指定SVN下，資料夾的結構
	 *		<br>創建時間：2013/9/24 下午6:14:56<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public String getSVN2dirTreeJSON(String url,String usr,String pwd) {
		DAVRepositoryFactory.setup();
		
		final DefaultSVNOptions options = new DefaultSVNOptions();
		final SVNClientManager manager = SVNClientManager.newInstance(options,usr,pwd); // 如果需要用戶名密碼
		SVNURL repositoryBaseUrl;
		
		try {
			repositoryBaseUrl = SVNURL.parseURIEncoded(url); // 傳入svn地址
			final SVNLogClient logClient = manager.getLogClient();
			final DirEntryHandler handler = new DirEntryHandler(); // 在svn
			logClient.doList(repositoryBaseUrl, SVNRevision.HEAD, SVNRevision.HEAD,false, true, handler); // 列出當前svn地址的目錄，對每個文件進行處理
			res = res.substring(0, res.length()-1) + "]";
		} catch (SVNException e) {
			errLog(e);
		}
		
		return res;
	}
	
	/**
	 * 回傳指定SVN下，包含檔案、資料丈的結構
	 *	@param url
	 *	@param usr
	 *	@param pwd
	 *	@return	
	 *	<br>創建時間：2013/10/31 上午9:33:39<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public String getSVN2fileTreeJSON(String url,String usr,String pwd) {
		DAVRepositoryFactory.setup();
		
		SVNClientManager manager;
		SVNURL repositoryBaseUrl;
		
		final DefaultSVNOptions options = new DefaultSVNOptions();
		manager = SVNClientManager.newInstance(options,usr,pwd); // 如果需要用戶名密碼
		try {
			repositoryBaseUrl = SVNURL.parseURIEncoded(url); // 傳入svn地址
			final SVNLogClient logClient = manager.getLogClient();
			final FileEntryHandler handler = new FileEntryHandler(); // 在svn
			logClient.doList(repositoryBaseUrl, SVNRevision.HEAD, SVNRevision.HEAD,false, true, handler); // 列出當前svn地址的目錄，對每個文件進行處理
			res = res.substring(0, res.length()-1) + "]";
		} catch (SVNException e) {
			errLog(e);
		}
		
		return res;
	}
	
	/**
	 *	查詢指定子系統，指定是MR還是OL，其所記錄的連線資訊
	 *	index 0,1,2 分別是 連線位址、登入帳號、登入密碼
	 *	type 1 是 CM MR 原始碼區, <br>
	 *	type 2 是 CM OL 原始碼區, <br>
	 *	type 3 是 AP MR 原始碼區, <br>
	 *	type 4 是 AP OL 原始碼區, <br>
	 *	type 5 是 AP 取得CM給的原始碼, <br>
	 *	type 6 是 CM MR 部署檔區, <br>
	 *	type 7 是 CM OL 部署檔區 <br>
	 *	<br>創建時間：2013/9/25 下午8:08:53<br>
	 *	創建人員：郭啟揚 (842297)
	 *
	 *
	 */
	public ArrayList<String> getSVN4SubsysConnInfo(String subsysId, int svnTypeId) throws Exception {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		final ArrayList<String> connSVNInfo = new ArrayList<String>();
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT svnUrl,svnUsr,svnPwd FROM mpsubsys2svn WHERE subsysId=? AND svnTypeId=?");
			ps.setString(1, subsysId );
			ps.setInt(2, svnTypeId );
			rs = ps.executeQuery();
			rs.next();
			
			connSVNInfo.add( DB2UI(rs.getString("svnUrl")) );
			connSVNInfo.add( DB2UI(rs.getString("svnUsr")) );
			connSVNInfo.add( DB2UI(rs.getString("svnPwd")) );
			
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return connSVNInfo;
	}
	
	
	
	
	public ArrayList<String> getSVNConn4Delivery(int fromSysNo,int targetSystemId) {
		final ArrayList<String> connInfo = new ArrayList<String>();
		
		final String sql = "SELECT loginURL,id,pwd FROM mpsyssvn t1,svnlogin t2 WHERE t1.fromSysSN=? AND t1.tarSysSN=? AND t1.svnLoginSN=t2.svnLoginSN";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, fromSysNo);
			ps.setInt(2, targetSystemId);
			rs = ps.executeQuery();
			rs.next();
			
			connInfo.add( rs.getString("loginURL") );
			connInfo.add( rs.getString("id") );
			connInfo.add( rs.getString("pwd") );
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) { e.printStackTrace(); }
		}
		
		return connInfo;
	}
	
	/**
	 * 取得指定 SVN repo的最新版次
	 *	@param url
	 *	@param usr
	 *	@param pwd
	 *	@return
	 *	@throws Exception	
	 *	<br>創建時間：2013/10/31 上午11:54:11<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public long getSVNver(String url,String usr,String pwd) throws Exception {
		DAVRepositoryFactory.setup();
		final SVNRepository repo = SVNRepositoryFactory.create(SVNURL.parseURIDecoded(url));
		final ISVNAuthenticationManager authManager = SVNWCUtil.createDefaultAuthenticationManager(usr, pwd);
		repo.setAuthenticationManager(authManager);
		final SVNDirEntry entry = repo.info(".", -1);
		repo.closeSession();
		return entry.getRevision();
	}
	
	/**	取得系統變數
	 * @param pn
	 * @return
	 * @throws Exception
	 */
	public String getSysParameter(String pn) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String res = "";
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT paraVal FROM configParameter WHERE paraName=?");
			ps.setString(1, pn);
			rs = ps.executeQuery();
			rs.next();
			res = rs.getString("paraVal");
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return res;
	}
	
	//取得至指定系統的 資料庫連線 
	public Connection getTarConnection(HttpServletRequest request,int targetSystemId) throws SQLException {
		//原本使用C3P0的 code  >"< 壞了 
		/*ServletContext ctx = request.getServletContext();
		Map<Integer,ComboPooledDataSource> mp = (Map<Integer, ComboPooledDataSource>) ctx.getAttribute("dataSourceMap");
		return mp.get(targetSystemId).getConnection();*/
		
		Connection conn = null;
		Connection tarConn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT t1.tarSysSN,t2.ip,t2.port,t2.id,t2.pwd,t2.dbName,t3.strProtocol FROM mpsysdb t1,dblogin t2,dbtype t3 WHERE t2.DBTypeSN=t3.DBTypeSN AND t1.dbLoginSN=t2.dbLoginSN AND t1.tarSysSN =?");
			ps.setInt(1, targetSystemId);
			rs = ps.executeQuery();
			rs.next();
			
			Class.forName("com.mysql.jdbc.Driver");
			String url = rs.getString("strProtocol")+"://"+rs.getString("ip")+":"+rs.getInt("port")+"/"+rs.getString("dbName");
			tarConn = DriverManager.getConnection( url,rs.getString("id"),rs.getString("pwd") );
			
		} catch (Exception e) {
			System.out.println(errProcess(e));
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) { e.printStackTrace(); }
		}
		return tarConn;
	}
	
	/**	取得遠端的流水號
	 * @param table
	 * @param conn
	 * @return
	 */
	public int getTarCurrentSN(String table, Connection conn) {

		String sql = "UPDATE envtable_seq SET seqcurrent = (seqcurrent + 1) WHERE seqname=?";
		int res = 0;
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, table);
			ps.execute();
			
			sql = "SELECT seqcurrent FROM envtable_seq WHERE seqname=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, table);
			rs = ps.executeQuery();
			rs.next();
			res = rs.getInt(1);
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
			} catch (Exception e) { e.printStackTrace(); }
		}
		
		return res;
	
	}
	
	
	
	public String getTarSVNConnection(int fromSysNo,int targetSystemId,String split) {
		String res = "";
		String sql = "SELECT svnLoginSN FROM mpsyssvn WHERE fromSysSN=? AND tarSysSN=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, fromSysNo);
			ps.setInt(2, targetSystemId);
			rs = ps.executeQuery();
			rs.next();
			
			int svnLoginSN = rs.getInt("svnLoginSN");
			rs.close();
			
			sql = "SELECT loginURL,id,pwd FROM svnlogin WHERE svnLoginSN=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, svnLoginSN);
			rs = ps.executeQuery();
			rs.next();
			
			res=rs.getString("id")+split+rs.getString("pwd")+split+rs.getString("loginURL");
			
		} catch (SQLException x) {
			System.out.println(errProcess(x));
		} catch (Exception e) {
			System.out.println(errProcess(e));
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) { e.printStackTrace(); }
		}
		
		return res;
	}
	
	/**
	 * 取得指定Datetime 的時間
	 * @param dt
	 * @return<p>
	 * Commented on:2014/3/20 下午8:02:27<br>
	 * Created by:  Arthur
	 */
	public String getTime(Date dt) {
		String result ="";
		if( dt != null ) {
			result = new java.text.SimpleDateFormat("HH:mm:ss").format(dt); 
		}
		
		return result;
	}
	
	/**
	 *	@param dataType	待辦事項的類型
	 *	@return	該筆待辦事項的中文描述
	 *	<br>創建時間：2013/10/7 下午2:27:12<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public String getToDoListDataTypeDesc(int dataType ) {
		String res="";
		
		if (dataType ==1 ) { //變更申請 (新增模組)
			res = "申請新增模組";
		} else if (dataType ==2 ) { //繳交
			res = "申請繳交";
		} else if (dataType ==3 ) { //等待安排部署時間
			res = "安排部署時間";
		} else if (dataType ==4 ) { //回覆測試結果
			res = "回覆測試結果";
		} else if (dataType ==5 ) { //變更申請 (異動模組)
			res = "申請變更模組";
		} else if (dataType ==6 ) { //申請相依性
			res = "申請定義模組相依性";
		} else if (dataType ==7 ) { //審核可否部署
			res = "審核可否部署";
		} else if (dataType ==8 ) { //確認部署結果
			res = "確認部署結果";
		}
		
		return res;
	}
	
	/**	查詢jenkins上，某個Job，其所屬最原始的提交號碼為何  (因為可能是被觸發而封裝的)
	 * @param jobName
	 * @param jobSN
	 * @return
	 */
	public I1S1 getTopJob(String jobName, int jobSN ) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		I1S1 i1s1 = new I1S1();
		
		try {
			conn = getConn();
			final String sql = "SELECT modIdp,jobSNp,chkInMainId FROM bemain WHERE modId=? AND jobSN=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, jobName);
			ps.setInt(2, jobSN);
			rs = ps.executeQuery();
			rs.next();
			final String upJobId = rs.getString("modIdp");
			final int upJobSN = rs.getInt("jobSNp");
			
			if ( "".equalsIgnoreCase( upJobId ) || upJobId== null || "null".equalsIgnoreCase( upJobId ) ) {
				i1s1.setI1( rs.getInt("chkInMainId") );
				i1s1.setS1( jobName );
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
				return i1s1;
			} else {
				i1s1 = getTopJob(upJobId,upJobSN);
			}
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return i1s1;
	}
	
	/**
	 * 建立與JBOSS的連線
	 *	@param defaultHost
	 *	@param defaultPort
	 *	@param username
	 *	@param password
	 *	@return
	 * @throws Exception 
	 */ 
	public CommandContext initCommandContext(String defaultHost,
			int defaultPort, String username, char[] password)
			throws Exception {
		
		CommandContext cmdCtx = null;
		try {
			
			cmdCtx = CommandContextFactory.getInstance().newCommandContext(defaultHost, defaultPort, username, password);
			cmdCtx.connectController();
			cmdCtx.handle("connect");	
		} catch(Exception e) {
			errLog(e);
			throw e;
		}
		 

		return cmdCtx;
	}
	
	
	//檢查子系統下是否存在待審中的相依性設定
	public boolean isDependencySetGoing (String subsysId) throws Exception {
		
		boolean isDependencySetGoing = false;
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement("SELECT * FROM cramain WHERE subsysId=? AND craStatusId=8");
			ps.setString(1, subsysId);
			rs = ps.executeQuery();
			if ( rs.next() ) { isDependencySetGoing = true; }
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs!=null ) {rs.close();}
			if ( ps!=null ) {ps.close();}
			if ( conn!=null ) {conn.close();}
		}
		
		return isDependencySetGoing;
	}
	
	/** 查詢指定的job，是否在jenkins 的Build排程中
	 * @param jobName
	 * @param isAuth
	 * @param urlStr
	 * @param usr
	 * @param pwd
	 * @return
	 * @throws Exception
	 */
	public boolean isEnqueue(String jobName,boolean isAuth,String urlStr,String usr,String pwd) throws Exception {
		boolean isEnqueue = false;
		
        final Map<String,Object> map = new HashMap<String,Object>();
        InputStream res = null ;
        // 連線取得資料
        res = URLConnectionUtil.doGet(urlStr,isAuth,usr,pwd,map);
		final SAXReader reader = new SAXReader();
		final Document document = reader.read(res);
		res.close();
		final Element root = document.getRootElement();
		if ( root.hasContent() ) {
			final Iterator<Element> it = root.elementIterator();
			while (it.hasNext()) {
				final Element e = it.next();
				if ( jobName.equalsIgnoreCase( e.element("task").element("name").getText() ) ) {
					isEnqueue = true;
				}
			}
		}
		return isEnqueue;
	}
	
	/**
	 *		確認指定子系統下，是否有為該子系統設定SVN
	 *		<br>創建時間：2013/9/25 下午7:54:21<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public boolean isExistSVN4Subsys(String subsysId,boolean isMR) throws Exception {
		
		boolean isExistSVN4Subsys = false;
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "";
		try {
			if ( isMR ) {
				sql = "SELECT subsysId FROM mpsubsys2svn WHERE subsysId=? AND svnTypeId=3 OR svnTypeId=4"; 
			} else {
				sql = "SELECT subsysId FROM mpsubsys2svn WHERE subsysId=? AND svnTypeId=1 OR svnTypeId=2";
			}
			
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, subsysId);
			rs = ps.executeQuery();
			if ( rs.next() ) { isExistSVN4Subsys = true; }
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return isExistSVN4Subsys;
	}
	
	
	/** 檢查檔案是否存在FTP上
	 * @param url
	 * @param usr
	 * @param pwd
	 * @param path
	 * @param fileName
	 * @return
	 * @throws JSchException
	 */
	
	/*
	 * 0 尚未測試	
	 * 2 主機不能連
	 * 3 帳密不通
	 * 4 連線正常
	 * 5不存在指定路徑
	 * 6檔案不存在
	 * */
	public int isFileOnFTP( String url,String usr,String pwd,String path,String fileName) throws JSchException {
		int res=0;
		
		final JSch jsch = new JSch();
        JSch.setConfig("StrictHostKeyChecking", "no");
        com.jcraft.jsch.Session session = null;
        ChannelSftp sftp = null;
		
        try {
        	session = jsch.getSession(usr, url);
    		session.setPassword( pwd );
    		try {
    			session.connect();
    			sftp = (ChannelSftp) session.openChannel("sftp");
    			sftp.connect();
    			sftp.cd("/");
    			System.out.println("檔案處理目前的位址："+sftp.pwd());
    			System.out.println("檔案處理要去的位址："+path);
    			cwdButNotmkdir(sftp,path);
    			res = 4;
    		} catch (Exception e) {
    			if ( "Auth fail".equalsIgnoreCase(e.getMessage()) ) {
    				res=3;	//帳密不通	
    			} else if(e.getMessage().contains("Connection timed out: connect") ) {
    				res=2;	//主機不能連
    			} else if ( "No such file".equalsIgnoreCase(e.getMessage()) ) {
    				res=5;	//不存在指定路徑
    			}
    		}
    		
    		if ( res ==4 ) {
    			try {
    				sftp.get(fileName);
    				System.out.println("檔案"+fileName+"是存在的");
    			} catch (Exception e) {
    				if ( "No such file".equalsIgnoreCase(e.getMessage()) ) {
    					res = 6; //檔案不存在
    				}
    			}
    		}
        } finally {
        	if ( sftp != null ) { if ( sftp.isConnected() ) { sftp.disconnect(); } }
			if ( session != null ) { if ( session.isConnected() ) { session.disconnect(); } }
        }
		
		
		return res;
	}
	
	/**	檢查job 是否在jenkin中，正在執行
	 * @param jobName
	 * @param isAuth
	 * @param urlStr
	 * @param usr
	 * @param pwd
	 * @return
	 * @throws Exception
	 */
	public boolean isJobBuilding(String jobName,boolean isAuth,String urlStr,String usr,String pwd) throws Exception {
		out.println("開始檢查"+jobName+"是否在Build Executor中");
		boolean isJobBuilding = false;
        final Map<String,Object> map = new HashMap<String,Object>();
        InputStream ins = null ;
        // 連線取得資料
        ins = URLConnectionUtil.doGet(urlStr,isAuth,usr,pwd,map);
		final SAXReader reader = new SAXReader();
		final Document document = reader.read(ins);
		ins.close();
		final Element root = document.getRootElement();
		
		final int totalExecutors = Integer.parseInt( root.element("totalExecutors").getText() );	//這台jenkins的執行排程大小
		final Iterator<Element> it = root.element("computer").elementIterator("executor");
		
		while (it.hasNext()) {
			final Element e = it.next();
			final boolean isRunning = !Boolean.parseBoolean( e.element("idle").getText() );	//這個 執行排程是否有在跑
			if ( isRunning ) {
				String rj = e.element("currentExecutable").element("url").getText();	//正在執行的job名稱
				rj = rj.substring((JENKINSURL+"jobs/").length()-1, rj.length()-1);
				rj = rj.substring(0, rj.lastIndexOf("/"));
				out.println("目前排程中的是"+rj);
				
				if( rj.equalsIgnoreCase(jobName) ) {
					isJobBuilding = true;
				}
			}
		}
		
		ins.close();
		out.println(jobName+"是否正在執行："+isJobBuilding);
		return isJobBuilding;
	}
	
	/**
	 *	@param jobName	欲比對的job名稱
	 *	@param isAuth	此jenkins是否要登入
	 *	@param urlStr	jenkins的連結位置
	 *	@param usr		jenkins的登入帳號
	 *	@param pwd		jenkins的登入密碼
	 *	@return	這個Job是否已存在
	 *	<br>創建時間：2013/10/22 上午11:07:53<br>
	 *	創建人員：郭啟揚 (842297)
	 * @throws Exception 
	 */
	public boolean isJobExistInJenkins(String jobName,boolean isAuth,String urlStr,String usr,String pwd) throws Exception {

		boolean isJobExistInJenkins = false;
		
        final Map<String,Object> map = new HashMap<String,Object>();
        InputStream res = null ;
        // 連線取得資料
        res = URLConnectionUtil.doGet(urlStr,isAuth,usr,pwd,map);
		final SAXReader reader = new SAXReader();
		final Document document = reader.read(res);
		final Element root = document.getRootElement();

		final Iterator<Element> it = root.elementIterator();

		while (it.hasNext()) {
			final Element e2 = it.next();
			
			if ("job".equalsIgnoreCase( e2.getName() ) ) {
				if ( jobName.equalsIgnoreCase( e2.element("name").getText() ) ) {
					isJobExistInJenkins = true;
				}
			}
		}
		
		res.close();
		
		return isJobExistInJenkins;
	}
	
	/** 檢查是否是數字
	 * @param str
	 * @return
	 */
	public boolean isNumaric( String str ) {
		try {
			Double.parseDouble(str);
		} catch (NumberFormatException nfe) {
			return false;
		}
		return true;
	}

	
	/**
	 *		確認此路徑,在此SVN中存在與否，這個"路徑"，可能是資料夾也可能是檔案
	 *		<br>創建時間：2013/9/30 上午9:34:19<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public boolean isPathExist(String path,String url,String usr,String pwd) throws SVNException {
		boolean isExist = false;
		
		final SVNRepository repo = SVNRepositoryFactory.create(SVNURL.parseURIEncoded(url));
		final ISVNAuthenticationManager authManager = SVNWCUtil.createDefaultAuthenticationManager(usr,pwd);
		repo.setAuthenticationManager(authManager);
		if ( SVNNodeKind.DIR == repo.checkPath(path, LATEST) || SVNNodeKind.FILE == repo.checkPath(path, LATEST) ) {
			isExist=true;
		}
		repo.closeSession();
		return isExist;
	}
	
	/**
	 * 檢查是否 Request.getparameter所讀的參數是存在的
	 * @param editType
	 * @return
	 */
	public boolean isReqExist(String editType) {
		boolean isRequestExist = false;
		if(editType!=null && !editType.isEmpty()){
			isRequestExist = true;
		}
		return isRequestExist;
	}
	
	/**	檢查這個需求是否已經存在
	 * 0 尚未測試
	 * 1  同樣的組合已經存在
	 * 2 reqSID已存在，但需求描述不是所輸入的
	 * 3  不存在
	 * @param reqSID
	 * @param reqDesc
	 * @return
	 */
	public int isReqExisted(String reqSID, String reqDesc) {
		int res = 0;
		String sql = "SELECT COUNT(*) FROM reqs WHERE reqSID=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, reqSID);
			rs = ps.executeQuery();
			rs.next();
			
			if ( rs.getInt(1) != 0 ) {	//這個ID已經被用了
				sql = "SELECT COUNT(*) FROM reqs WHERE reqSID=? AND reqDesc=?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, reqSID);
				ps.setString(2, reqDesc);
				rs = ps.executeQuery();
				rs.next();
				if ( rs.getInt(1) != 0 ) {	//同樣的組合已經存在
					res =1;
				} else { //reqSID已存在，但需求描述不是所輸入的
					res=2;
				}
			} else {
				res =3;
			}
			
		} catch (Exception x) {
			errLog(x);
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) { e.printStackTrace(); }
		}
		
		return res;
	}
	
	
	/**
	 *		取自 http://stackoverflow.com/questions/6730009/validate-a-file-name-on-windows<br>
	 *		檢查此名稱是否符合資料夾的命名規範(windows)
	 *		<br>創建時間：2013/10/2 上午10:22:01<br>
	 *		創建人員：郭啟揚 (842297)
	 */
	public boolean isValidDirName(String text)
	{
		final Pattern pattern = Pattern.compile(
	        "# Match a valid Windows filename (unspecified file system).          \n" +
	        "^                                # Anchor to start of string.        \n" +
	        "(?!                              # Assert filename is not: CON, PRN, \n" +
	        "  (?:                            # AUX, NUL, COM1, COM2, COM3, COM4, \n" +
	        "    CON|PRN|AUX|NUL|             # COM5, COM6, COM7, COM8, COM9,     \n" +
	        "    COM[1-9]|LPT[1-9]            # LPT1, LPT2, LPT3, LPT4, LPT5,     \n" +
	        "  )                              # LPT6, LPT7, LPT8, and LPT9...     \n" +
	        "  (?:\\.[^.]*)?                  # followed by optional extension    \n" +
	        "  $                              # and end of string                 \n" +
	        ")                                # End negative lookahead assertion. \n" +
	        "[^<>:\"/\\\\|?*\\x00-\\x1F]*     # Zero or more valid filename chars.\n" +
	        "[^<>:\"/\\\\|?*\\x00-\\x1F\\ .]  # Last char is not a space or dot.  \n" +
	        "$                                # Anchor to end of string.            ", 
	        Pattern.CASE_INSENSITIVE | Pattern.UNICODE_CASE | Pattern.COMMENTS);
		final Matcher matcher = pattern.matcher(text);
		final boolean isMatch = matcher.matches();
	    return isMatch;
	}
	
	/**	查詢子系統下所有的模組
	 * @param subsysId
	 * @param empId
	 * @return
	 * @throws Exception
	 */
	public ArrayList<String> queryAllMods(String subsysId) throws Exception {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		final ArrayList<String> mods = new ArrayList<String>();
		
		final String sql="SELECT modUnitId FROM modunits WHERE modUnitStatusId=1 AND subsysId=?";
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, subsysId);
			rs = ps.executeQuery();
			
			while (rs.next()) { mods.add( rs.getString("modUnitId") ); }
			
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return mods;
	}
	
	/**
	 * 	取得人員在指定系統下可以使用的變更申請
	 *	@param subsysId
	 *	@param empId
	 *	@return	
	 *	<br>創建時間：2013/10/24 下午5:11:49<br>
	 *	創建人員：郭啟揚 (842297)
	 * @throws Exception 
	 */
	public ArrayList<OpenCRA> queryAvailableCRA(String subsysId,String empId) throws Exception {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		final ArrayList<OpenCRA> cras = new ArrayList<OpenCRA>();
		
		//final String sql="SELECT DISTINCT t2.craId,t2.craDetId,t2.modUnitId FROM mpcraperformers t1,cradetail t2,cramain t3 WHERE t3.subsysId=? AND t2.isOpen='t' AND t2.craId=t3.craId AND t1.craDetId=t2.craDetId AND t3.craStatusId=5 AND t1.craId=t2.craId AND ((t1.performerId in (SELECT t4.roleId FROM mpemprolesubsys t4 WHERE t4.subsysId=t3.subsysId AND t4.empId=?)  AND t1.isRole='t') OR (t1.performerId=? AND t1.isRole='f') )";
		final String sql="SELECT DISTINCT t2.modUnitId FROM mpcraperformers t1,cradetail t2,cramain t3, reqs t4, mpcrareq t5 WHERE t4.isClosed='f' AND t4.reqId=t5.reqId AND t5.craId=t3.craId AND t5.craDetId=t2.craDetId AND t3.subsysId=? AND t2.isOpen='t' AND t2.craId=t3.craId AND t1.craDetId=t2.craDetId AND t3.craStatusId=5 AND t1.craId=t2.craId AND ((t1.performerId in (SELECT t4.roleId FROM mpemprolesubsys t4 WHERE t4.subsysId=t3.subsysId AND t4.empId=?) AND t1.isRole='t') OR (t1.performerId=? AND t1.isRole='f') )";
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, subsysId);
			ps.setString(2, empId);
			ps.setString(3, empId);
			rs = ps.executeQuery();
			
			while (rs.next()) {
				final OpenCRA cra = new OpenCRA();
				cra.setModId(rs.getString("modUnitId"));
				cras.add(cra);
			}
			
		} catch (Exception e) {
			throw e;
		} finally {
			if ( rs !=null ) {rs.close();}
			if ( ps !=null ) {ps.close();}
			if ( conn !=null ) {conn.close();}
		}
		
		return cras;
	}
	
	/**	取得 F部署目前最大的序號
	 * @return
	 */
	public int queryMaxFdeplMainSN() {
		int fDeplmainSN=0;
		
		String sql = "SELECT MAX(fDeplmainSN) FROM fdeplmain";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			fDeplmainSN = rs.getInt(1);
		} catch (Exception x) {
			errLog(x);
		} finally {
			try {
				if ( rs !=null ) { rs.close(); }
				if ( ps !=null ) { ps.close(); }
				if ( conn !=null ) { conn.close(); }	
			} catch (Exception e) { e.printStackTrace(); }
		}
		
		return fDeplmainSN;
	}
	
	/**	取得 模組，在創建時，指定的 SVN MR來源與 SVN OL來源
	 * @param subsysId
	 * @param modId
	 * @return
	 */
	public S2 querySourceSVN(String subsysId,String modId) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		S2 s2= new S2();
		try {
			conn = getConn();
			final String sql = "SELECT olSVNpath,mrSVNpath FROM modunits WHERE modUnitId=? AND subsysId=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, modId);
			ps.setString(2, subsysId);
			rs = ps.executeQuery();
			rs.next();
			s2.setS1( rs.getString("mrSVNpath") );
			s2.setS2( rs.getString("olSVNpath") );
		} catch (Exception e) {
			errLog(e);
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return s2;
	}
	
	public void recoverJenkinsJobSVNver(String jobName) throws Exception{
		final String urlStr =JENKINSURL+"job/"+jobName+"/config.xml";
		
		final Map<String,Object> map = new HashMap<String,Object>();
        InputStream ins = null ;
        // 連線取得資料
        ins = URLConnectionUtil.doGet(urlStr,true,JENKINS_USR,JENKINS_PWD,map);
		final Document document = new SAXReader().read(ins);
		ins.close();
		final Element root = document.getRootElement();
		Iterator<Element> it = root.elementIterator();
		
		while (it.hasNext()) {
			final Element e1 = it.next();
			if ( "scm".equalsIgnoreCase(e1.getName() ) ){
				String remote = e1.element("locations").element("hudson.scm.SubversionSCM_-ModuleLocation").element("remote").getText();
				if(remote.contains("@")) {
					remote = remote.substring(0,remote.lastIndexOf("@"));
				}
				e1.element("locations").element("hudson.scm.SubversionSCM_-ModuleLocation").element("remote").setText(remote);
			}
		}
		
		final PostMethod postMethod = new PostMethod(urlStr);
		postMethod.setRequestHeader("Content-type","application/xml; charset=UTF-8");
		postMethod.setRequestBody(new FileInputStream( saveToFile(new File("tmp"+System.currentTimeMillis()+".xml"), document) ));
			
		final HttpClient client = new HttpClient();
		client.getParams().setAuthenticationPreemptive(true);
		client.getState().setCredentials( AuthScope.ANY, new UsernamePasswordCredentials(JENKINS_USR,JENKINS_PWD) );
		client.executeMethod(postMethod);
	}
	
	/**
	 * 移除jobName中的 removeItem
	 * @param jobName
	 * @param removeItem
	 * @throws Exception
	 */
	public void removeJobDownStreamItem(String jobName,String removeItem) throws Exception {
		
		final String urlStr =JENKINSURL+"job/"+jobName+"/config.xml";
		final Map<String,Object> map = new HashMap<String,Object>();
        InputStream ins = null ;
        // 連線取得資料
        ins = URLConnectionUtil.doGet(urlStr,true,JENKINS_USR,JENKINS_PWD,map);
		final SAXReader reader = new SAXReader();
		final Document document = reader.read(ins);
		ins.close();
		final Element root = document.getRootElement();		
		Iterator<Element> it = root.elementIterator();
		
		while( it.hasNext() ) {
			Element e = it.next();
			if( "publishers".equalsIgnoreCase( e.getName() ) ) {
				it = e.elementIterator();
				while( it.hasNext() ) {
					e = it.next();
					if( "hudson.tasks.BuildTrigger".equalsIgnoreCase( e.getName() ) ) {
						it = e.elementIterator();
						while( it.hasNext() ) {
							e = it.next();
							if( "childProjects".equalsIgnoreCase( e.getName() ) ) {
								final Element e3 = document.getRootElement().element("publishers").element("hudson.tasks.BuildTrigger").element("childProjects");
								String ds = e3.getText();
								out.println("目前"+jobName+"的下游是"+ds+", 我要移掉"+removeItem);

								if ( ds.equalsIgnoreCase(removeItem) ) {
									document.getRootElement().element("publishers").clearContent();
								} else if ( ds.contains(removeItem) ) {
									if ( ds.contains( removeItem+"," ) ) {
										ds = ds.replaceAll(removeItem+",", "");
									} else {
										ds = ds.replaceAll(","+removeItem, "");
									}
									
									if ("".equalsIgnoreCase(ds)) {
										document.getRootElement().element("publishers").clearContent();
									} else {
										e3.setText(ds);	
									}
								}
								
								final PostMethod postMethod = new PostMethod(urlStr);
								postMethod.setRequestHeader("Content-type","application/xml; charset=UTF-8");
								postMethod.setRequestBody(new FileInputStream( saveToFile(new File("tmp"+System.currentTimeMillis()+".xml"), document) ));
								
								final HttpClient client = new HttpClient();
								client.getParams().setAuthenticationPreemptive(true);
								client.getState().setCredentials( AuthScope.ANY, new UsernamePasswordCredentials(JENKINS_USR,JENKINS_PWD) );
								client.executeMethod(postMethod);							
							}
						}
					}
				}
			}
		}
		
		
		
		
		
		
		
		
		
	}
	
	/**
	 * 執行完畢，將該將job重新設回原本的狀態
	 * @param jobName
	 * @throws Exception
	 */
	public void resetJobPostBuildURL(String jobName) throws Exception {
		final String urlStr =JENKINSURL+"job/"+jobName+"/config.xml";	//job在jenkins中，設定的xml檔
		final String parameters = "?jobName="+jobName;
		final Map<String,Object> map = new HashMap<String,Object>();
        InputStream ins = null ;
        Document document = null;
        try {
        	ins = URLConnectionUtil.doGet(urlStr,true,JENKINS_USR,JENKINS_PWD,map);
    		document = new SAXReader().read(ins);
        } catch(Exception e) {
        	errLog(e);
        	throw e;
        } finally {
        	ins.close();
        }
		
		final Element root = document.getRootElement();
		final Iterator<Element> it = root.elementIterator();
		Element jph = null;
		
		while (it.hasNext()) {
			final Element element1 = it.next();
			if ( "postbuilders".equalsIgnoreCase(element1.getName() ) ){
				element1.detach();
			}
		}
		jph = root.addElement("postbuilders");
		Element singleConditionalBuilderSuccess = jph.addElement("org.jenkinsci.plugins.conditionalbuildstep.singlestep.SingleConditionalBuilder").addAttribute("plugin","conditional-buildstep@1.3.3");
		setBuildBulk( singleConditionalBuilderSuccess,parameters,"TriggerNext","SUCCESS",0,"BLUE" );
		
		Element singleConditionalBuilderFail = jph.addElement("org.jenkinsci.plugins.conditionalbuildstep.singlestep.SingleConditionalBuilder").addAttribute("plugin","conditional-buildstep@1.3.3");
		setBuildBulk( singleConditionalBuilderFail,parameters,"JobExecutionFail","FAILURE",2,"RED" );
				
		final PostMethod postMethod = new PostMethod(urlStr);
		postMethod.setRequestHeader("Content-type","application/xml; charset=UTF-8");
		postMethod.setRequestBody(new FileInputStream( saveToFile(new File("tmp"+System.currentTimeMillis()+".xml"), document) ));
			
		final HttpClient client = new HttpClient();
		client.getParams().setAuthenticationPreemptive(true);
		client.getState().setCredentials( AuthScope.ANY, new UsernamePasswordCredentials(JENKINS_USR,JENKINS_PWD) );
		client.executeMethod(postMethod);
	}
	
	private File saveToFile(File storedFile,Document document) {
		if (storedFile.exists())
			storedFile.delete();

		FileOutputStream fos = null;
		OutputStreamWriter osw = null;
		XMLWriter writer = null;
		try {
			storedFile.createNewFile();

			final OutputFormat format = OutputFormat.createPrettyPrint();
			format.setEncoding("utf-8");
			fos = new FileOutputStream(storedFile);
			osw = new OutputStreamWriter(fos, Charset.forName("utf-8"));
			writer = new XMLWriter(osw, format);
			writer.write(document);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (writer != null) writer.close();
				if (osw != null) osw.close();
				if (fos != null) fos.close();
			} catch (IOException e) {
			}
		}
		return storedFile;
	}
	
	/**
	 *	寄發通知信
	 *	@param recipients	收件人員清單  String[0]姓名, String[1] Email Addr
	 *	@param subject		信件主旨
	 *	@param msgBody		信件內容
	 *	@throws MessagingException
	 *	@throws UnsupportedEncodingException	
	 *	<br>創建時間：2013/10/8 下午2:42:04<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public void sendMailMain(ArrayList<String[]> recipients, String subject, String msgBody) throws MessagingException, UnsupportedEncodingException {
		
		final String fromAddr = "fakeEmailAddr@cht.com.tw";	//公司內的Server一定要一個fromAddr，只好就捏造一個
		final String fromAddrName = "程式上線更版系統";
		
		final Properties prop = new Properties();
		prop.put(mailServer, mailServerIP); 

		final Session session = Session.getDefaultInstance(prop, null); 
		final MimeMessage msg = new MimeMessage(session);

		msg.setSubject(subject);
		final InternetAddress from = new InternetAddress(fromAddr);
		from.setPersonal(fromAddrName, mailLocale);
		msg.setFrom(from);

		final InternetAddress to = new InternetAddress();
		
		for(int i=0; i<recipients.size();i++) {
			to.setPersonal(recipients.get(i)[0], mailLocale);
			to.setAddress(recipients.get(i)[1]);
			msg.addRecipient(RecipientType.TO, to);			
		}

		final Multipart mp = new MimeMultipart();
		final MimeBodyPart mbp1 = new MimeBodyPart();
		mbp1.setContent(msgBody, "text/html;charset=UTF-8");
		mp.addBodyPart(mbp1);
		msg.setContent(mp);
		Transport.send(msg);
	}
	
	/**
	 * 寄發通知信
	 *	@param fromAddr		寄件者email
	 *	@param fromAddrName	寄件者姓名
	 *	@param recipients	收件人員清單  String[0]姓名, String[1] Email Addr
	 *	@param subject		信件主旨
	 *	@param msgBody		信件內容
	 *	@throws MessagingException
	 *	@throws UnsupportedEncodingException	
	 *	<br>創建時間：2013/10/8 下午2:39:54<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public void sendMailMain(String fromAddr, String fromAddrName, ArrayList<String[]> recipients, String subject, String msgBody) throws MessagingException, UnsupportedEncodingException {
		
		final Properties prop = new Properties();
		prop.put(mailServer, mailServerIP); 

		final Session session = Session.getDefaultInstance(prop, null); 

		final MimeMessage msg = new MimeMessage(session);

		//msg.setSubject(new String(subject.getBytes(locale), locale), locale);
		msg.setSubject(subject);
		final InternetAddress from = new InternetAddress(fromAddr);
		from.setPersonal(fromAddrName, mailLocale);
		msg.setFrom(from);

		final InternetAddress to = new InternetAddress();
		
		for(int i=0; i<recipients.size();i++) {
			to.setPersonal(recipients.get(i)[0], mailLocale);
			to.setAddress(recipients.get(i)[1]);
			msg.addRecipient(RecipientType.TO, to);			
		}

		final Multipart mp = new MimeMultipart();

		final MimeBodyPart mbp1 = new MimeBodyPart();
		mbp1.setContent(msgBody, "text/html;charset=UTF-8");
		mp.addBodyPart(mbp1);
		msg.setContent(mp);
		Transport.send(msg);
	}
	
	/**
	 *	寄發通知信
	 *	@param recipient	收件人員  recipient[0]姓名, recipient[1] Email Addr
	 *	@param subject		信件主旨
	 *	@param msgBody		信件內容
	 *	@throws MessagingException
	 *	@throws UnsupportedEncodingException	
	 *	<br>創建時間：2013/10/8 下午2:51:50<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public void sendMailMain(String[] recipient, String subject, String msgBody) throws MessagingException, UnsupportedEncodingException {
		
		final String fromAddr = "fakeEmailAddr@cht.com.tw";	//公司內的Server一定要一個fromAddr，只好就捏造一個
		final String fromAddrName = "程式上線更版系統";
		
		final Properties prop = new Properties();
		prop.put(mailServer, mailServerIP); 

		final Session session = Session.getDefaultInstance(prop, null); 
		final MimeMessage msg = new MimeMessage(session);

		msg.setSubject(subject);
		final InternetAddress from = new InternetAddress(fromAddr);
		from.setPersonal(fromAddrName, mailLocale);
		msg.setFrom(from);

		final InternetAddress to = new InternetAddress();
		
		to.setPersonal(recipient[0], mailLocale);
		to.setAddress(recipient[1]);
		msg.addRecipient(RecipientType.TO, to);			
	
		final Multipart mp = new MimeMultipart();
		final MimeBodyPart mbp1 = new MimeBodyPart();
		mbp1.setContent(msgBody, "text/html;charset=UTF-8");
		mp.addBodyPart(mbp1);
		msg.setContent(mp);
		Transport.send(msg);
	}
	
	private void setBuildBulk(Element ele,String parameters,String servletName,String status,int ordinal,String color) {
		
		String postBuildURL = APSERVER_HOST+servletName+parameters;
		
		Element condition = ele.addElement("condition").addAttribute("class", "org.jenkins_ci.plugins.run_condition.core.StatusCondition").addAttribute("plugin","run-condition@1.0");
		Element worstResult = condition.addElement("worstResult");
		worstResult.addElement("name").setText( status );
		worstResult.addElement("ordinal").setText( String.valueOf( ordinal ) );
		worstResult.addElement("color").setText( color );
		worstResult.addElement("completeBuild").setText("true");
		Element bestResult = condition.addElement("bestResult");
		bestResult.addElement("name").setText( status );
		bestResult.addElement("ordinal").setText( String.valueOf( ordinal ) );
		bestResult.addElement("color").setText( color );
		bestResult.addElement("completeBuild").setText("true");
		
		Element httpRequest = ele.addElement("buildStep").addAttribute("class", "jenkins.plugins.http_request.HttpRequest").addAttribute("plugin","http_request@1.8.3");
		httpRequest.addElement("url").setText(postBuildURL);
		httpRequest.addElement("httpMode").setText("POST");
		
		ele.addElement("runner").addAttribute("class", "org.jenkins_ci.plugins.run_condition.BuildStepRunner$Fail").addAttribute("plugin","run-condition@1.0");
	}
	
	public String setJobDownstreamOnJenkins(String ds,boolean isAuth,String urlStr,String usr,String pwd) throws Exception {
		String res2="";
		//修改 xml node中的 p
		
		final Map<String,Object> map = new HashMap<String,Object>();
        final InputStream res = URLConnectionUtil.doGet(urlStr,isAuth,usr,pwd,map);
		final SAXReader reader = new SAXReader();
		final Document document = reader.read(res);
		res.close();
		final Element root = document.getRootElement();

		final Iterator<Element> it = root.elementIterator();

		while (it.hasNext()) {
			final Element e1 = it.next();
			if ( "publishers".equalsIgnoreCase(e1.getName() ) ){
				
				if( e1.elementIterator().hasNext() ){
					final Element e2 = e1.element("hudson.tasks.BuildTrigger");
					root.element("publishers").remove(e2);
				}
				if ( !"".equalsIgnoreCase(ds)) {
					root.element("publishers").addElement("hudson.tasks.BuildTrigger").addElement("childProjects").setText(ds);
					root.element("publishers").element("hudson.tasks.BuildTrigger").addElement("threshold");
					root.element("publishers").element("hudson.tasks.BuildTrigger").element("threshold").addElement("name").setText("SUCCESS");
					root.element("publishers").element("hudson.tasks.BuildTrigger").element("threshold").addElement("color").setText("BLUE");
				}
				
				
				final PostMethod postMethod = new PostMethod(urlStr);
				postMethod.setRequestHeader("Content-type","application/xml; charset=UTF-8");
				postMethod.setRequestBody(new FileInputStream( saveToFile(new File("tmp"+System.currentTimeMillis()+".xml"), document) ));
				
				final HttpClient client = new HttpClient();
				if (isAuth){
					client.getParams().setAuthenticationPreemptive(true);
					client.getState().setCredentials( AuthScope.ANY, new UsernamePasswordCredentials(usr,pwd) );	
				}
				res2 = client.executeMethod(postMethod)==200?"OK":"failure";
			}
		}
		
		
		return res2;
	}
	
	/**
	 * 將傳出的字串，若為null或為字串"null"，則回傳空字串""
	 * @param str
	 * @return<p>	原字串或空字串
	 * Commented on:2014/3/14 上午9:07:30<br>
	 * Created by:  Arthur
	 */
	public String stringFilter(String str) {
		String res=null;
		
		if ( isReqExist(str) ) {
			res = str;
		}
		if ( res == null || "null".equalsIgnoreCase(res) ) {
			res = "";	//如果沒有值，回傳空字串不要會傳null
		}
		
		return res;
	}
	
	/** 驅動jenkins部署
	 * @param jobName
	 * @return
	 * @throws Exception
	 */
	public int triggerJenkinsBuild(String jobName) throws Exception {
		
		final String urlStr =JENKINSURL+"job/"+jobName+"/build?token="+jobName;
        // 連線取得資料
		final PostMethod postMethod = new PostMethod(urlStr);
		postMethod.setRequestHeader("Content-type","application/xml; charset=UTF-8");
		
		final HttpClient client = new HttpClient();
		client.getParams().setAuthenticationPreemptive(true);
		client.getState().setCredentials( AuthScope.ANY, new UsernamePasswordCredentials(JENKINS_USR,JENKINS_PWD) );
		final int returnCode = client.executeMethod(postMethod);
		System.out.println("觸發遠端job所使用的URL是："+urlStr+"\n回傳值是："+returnCode);
		return returnCode;
	}
	
	/**
	 *	將UI來的字串，其編碼轉成DB的編碼
	 *	<br>創建時間：2013/9/24 上午11:17:02<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public String UI2DB( String str ) throws UnsupportedEncodingException {
		if ( isCodingEqual ) return str;
		else return ( new String( str.getBytes( UIcodeSet ), DBcodeSet ) );
	}
	
	
	/**	修改Build版封裝的執行狀態
	 *	@param modId
	 *	@param jobSN
	 *	@param beStatusId
	 *	@throws Exception	
	 *	<br>創建時間：2013/11/22 下午3:00:17<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public void updateBEstatus(String modId,int jobSN, int beStatusId) throws Exception {
		final Connection conn = getConn();
		PreparedStatement ps = null;
		
		final String sql = "UPDATE bemain SET beStatusId=? WHERE modId=? AND jobSN=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, beStatusId);
		ps.setString(2, modId);
		ps.setInt(3, jobSN);
		ps.execute();
		ps.close();
		conn.close();
	}
	
	
	/**	修改Build版封裝的執行狀態
	 *	@param modId
	 *	@param jobSN
	 *	@param beStatusId
	 *	@throws Exception	
	 *	<br>創建時間：2013/11/22 下午3:00:17<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public void updateBEstatus(String modId,int jobSN, int beStatusId,String depFileName, long deployFileVer) throws Exception {
		final Connection conn = getConn();
		PreparedStatement ps = null;
		
		final String sql = "UPDATE bemain SET beStatusId=?,depFileName=?,deployFileVer=? WHERE modId=? AND jobSN=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, beStatusId);
		ps.setString(2, depFileName);
		ps.setLong(3, deployFileVer);
		ps.setString(4, modId);
		ps.setInt(5, jobSN);
		ps.execute();
		ps.close();
		conn.close();
	}
	
	/**	修改繳交記錄的執行狀態
	 * 
	 *	@param chkInId
	 *	@param chkStatusId
	 *	@throws Exception	
	 *	<br>創建時間：2013/11/22 下午1:46:44<br>
	 *	創建人員：郭啟揚 (842297)
	 */
	public void updateCHKINstatus(int chkInId, int chkStatusId) throws Exception {
		
		final Connection conn = getConn();
		PreparedStatement ps = null;
		
		final String sql = "UPDATE chkinmain SET chkInStatusId=? WHERE chkInMainId=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, chkStatusId);
		ps.setInt(2, chkInId);
		ps.execute();
		ps.close();
		conn.close();
	}
	
	/**修改交付事件，目前的狀態
	 * 
	 * @param deliverySN
	 * @param statusSN
	 * @param targetSystemId<p>
	 * Commented on:2014/3/12 上午9:51:19<br>
	 * Created by:  Arthur
	 */
	public void updateDeltaskStatus(int deliverySN,int statusSN,Connection conn ) {
		final String sql = "UPDATE deltask SET statusSN=? WHERE deliverySN=?";
		
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, statusSN);
			ps.setInt(2, deliverySN);
			ps.execute();
		} catch (Exception e) {
			out.println(errLog(e));
		} finally {
			try {
				if ( ps !=null ) { ps.close(); }
				if ( conn !=null ) { conn.close(); }
			} catch (Exception e) { e.printStackTrace(); }
		}
	}
	
	
	/**
	 * 更新jenkins中，Job的postBuildStep
	 * 
	 * @param currentJobName	目前的job名稱
	 * @param chkInId			繳交ID
	 * @param subsysId			子系統ID
	 * @param performerId		繳交人員ID
	 * @param upstreamJobName	上游Job名稱
	 * @param upJobBuildNumber	上游Job的執行序號
	 * @param rootJobName		這次被觸發的Job名稱
	 * @param rootJobBuildNumber這次被觸發的Job執行序號
	 * @throws Exception
	 */
	public void updateJenkinsJobPostBuildURL(String currentJobName,int chkInId,String subsysId,String performerId,String upstreamJobName,int upJobBuildNumber,String rootJobName,int rootJobBuildNumber) throws Exception {
		final String urlStr =JENKINSURL+"job/"+currentJobName+"/config.xml";
		String parameters = "?jobName="+currentJobName+"&chkInId="+chkInId+"&subsysId="+subsysId+"&performerId="+performerId+"&upstreamJobName="+upstreamJobName+"&upJobBuildNumber="+upJobBuildNumber+"&rootJobName="+rootJobName+"&rootJobBuildNumber="+rootJobBuildNumber;
		
		final Map<String,Object> map = new HashMap<String,Object>();
        InputStream ins = null ;
        // 連線取得資料
        ins = URLConnectionUtil.doGet(urlStr,true,JENKINS_USR,JENKINS_PWD,map);
		final Document document = new SAXReader().read(ins);
		ins.close();
		final Element root = document.getRootElement();
		Iterator<Element> it = root.elementIterator();
		Element jph = null;
		
		
		while (it.hasNext()) {
			final Element e1 = it.next();
			if ( "postbuilders".equalsIgnoreCase(e1.getName() ) ){
				e1.detach();
			}
		}
		jph = root.addElement("postbuilders");
		Element singleConditionalBuilderSuccess = jph.addElement("org.jenkinsci.plugins.conditionalbuildstep.singlestep.SingleConditionalBuilder").addAttribute("plugin","conditional-buildstep@1.3.3");
		setBuildBulk( singleConditionalBuilderSuccess,parameters,"TriggerNext","SUCCESS",0,"BLUE" );
		
		Element singleConditionalBuilderFail = jph.addElement("org.jenkinsci.plugins.conditionalbuildstep.singlestep.SingleConditionalBuilder").addAttribute("plugin","conditional-buildstep@1.3.3");
		setBuildBulk( singleConditionalBuilderFail,parameters,"JobExecutionFail","FAILURE",2,"RED" );
				
		final PostMethod postMethod = new PostMethod(urlStr);
		postMethod.setRequestHeader("Content-type","application/xml; charset=UTF-8");
		postMethod.setRequestBody(new FileInputStream( saveToFile(new File("tmp"+System.currentTimeMillis()+".xml"), document) ));
			
		final HttpClient client = new HttpClient();
		client.getParams().setAuthenticationPreemptive(true);
		client.getState().setCredentials( AuthScope.ANY, new UsernamePasswordCredentials(JENKINS_USR,JENKINS_PWD) );
		client.executeMethod(postMethod);
	}
	
	
	public void updateJenkinsJobSVNver(String jobName,int ver) throws Exception{
		final String urlStr =JENKINSURL+"job/"+jobName+"/config.xml";
		
		final Map<String,Object> map = new HashMap<String,Object>();
        InputStream ins = null ;
        // 連線取得資料
        ins = URLConnectionUtil.doGet(urlStr,true,JENKINS_USR,JENKINS_PWD,map);
		final Document document = new SAXReader().read(ins);
		ins.close();
		final Element root = document.getRootElement();
		Iterator<Element> it = root.elementIterator();
		
		while (it.hasNext()) {
			final Element e1 = it.next();
			if ( "scm".equalsIgnoreCase(e1.getName() ) ){
				String remote = e1.element("locations").element("hudson.scm.SubversionSCM_-ModuleLocation").element("remote").getText();
				System.out.println("變更前的URL："+remote);
				remote += "@"+ver;
				e1.element("locations").element("hudson.scm.SubversionSCM_-ModuleLocation").element("remote").setText(remote);
				System.out.println("目前的URL："+ e1.element("locations").element("hudson.scm.SubversionSCM_-ModuleLocation").element("remote").getText() );
			}
		}
		
		final PostMethod postMethod = new PostMethod(urlStr);
		postMethod.setRequestHeader("Content-type","application/xml; charset=UTF-8");
		postMethod.setRequestBody(new FileInputStream( saveToFile(new File("tmp"+System.currentTimeMillis()+".xml"), document) ));
			
		final HttpClient client = new HttpClient();
		client.getParams().setAuthenticationPreemptive(true);
		client.getState().setCredentials( AuthScope.ANY, new UsernamePasswordCredentials(JENKINS_USR,JENKINS_PWD) );
		client.executeMethod(postMethod);
	}
	
	
	/**
	 * 修改目標job的  postBuild 執行 servlet的參數
	 * @param jobName
	 * @param chkInId
	 * @throws Exception 
	 */
	public void updateJobPostBuildURL(String jobName,int chkInId,String UpJobName,int UpJobSN,String rootJobName,int rootJobSerialNumber,String performerId) throws Exception {
		final String urlStr =JENKINSURL+"job/"+jobName+"/config.xml";
		String postBuildURL = "";
		if (chkInId == 0) {
			postBuildURL = APSERVER_HOST+"TriggerNext?jobName="+jobName+"&UpJobName="+UpJobName+"&UpJobSN="+UpJobSN+"&rootJobName="+rootJobName+"&rootJobSerialNumber="+rootJobSerialNumber+"&performerId="+performerId;			
		} else {
			postBuildURL = APSERVER_HOST+"TriggerNext?jobName="+jobName+"&chkInId="+chkInId+"&UpJobName="+UpJobName+"&UpJobSN="+UpJobSN+"&rootJobName="+rootJobName+"&rootJobSerialNumber="+rootJobSerialNumber+"&performerId="+performerId;
		}
		final Map<String,Object> map = new HashMap<String,Object>();
        InputStream ins = null ;
        // 連線取得資料
        ins = URLConnectionUtil.doGet(urlStr,true,JENKINS_USR,JENKINS_PWD,map);
		final SAXReader reader = new SAXReader();
		final Document document = reader.read(ins);
		ins.close();
		final Element root = document.getRootElement();
		Iterator<Element> it = root.elementIterator();
		Element jph = null;
		
		boolean isNotExist = true;
		
		while (it.hasNext()) {
			final Element e1 = it.next();
			if ( "postbuilders".equalsIgnoreCase(e1.getName() ) ){
				isNotExist = false;
			}
		}
		
		if ( isNotExist ) {	//如果不存在postbuilders，就直接加上
			jph = root.addElement("postbuilders");
			jph = jph.addElement("jenkins.plugins.http__request.HttpRequest").addAttribute("plugin","http_request@1.8.3");
			jph.addElement("url").setText(postBuildURL);
			jph.addElement("httpMode").setText("POST");
		} else {
			it = root.elementIterator();
			while (it.hasNext()) {
				final Element e1 = it.next();
				if ( "postbuilders".equalsIgnoreCase(e1.getName() ) ){
					e1.detach();
					jph = root.addElement("postbuilders");
					jph = jph.addElement("jenkins.plugins.http__request.HttpRequest").addAttribute("plugin","http_request@1.8.3");
					jph.addElement("url").setText(postBuildURL);
					jph.addElement("httpMode").setText("POST");
				}
			}
		}
				
		final PostMethod postMethod = new PostMethod(urlStr);
		postMethod.setRequestHeader("Content-type","application/xml; charset=UTF-8");
		postMethod.setRequestBody(new FileInputStream( saveToFile(new File("tmp"+System.currentTimeMillis()+".xml"), document) ));
			
		final HttpClient client = new HttpClient();
		client.getParams().setAuthenticationPreemptive(true);
		client.getState().setCredentials( AuthScope.ANY, new UsernamePasswordCredentials(JENKINS_USR,JENKINS_PWD) );
		client.executeMethod(postMethod);
	}
}